Home » Server Options » Spatial » Help with Spatial Query (oracle 11g)
Help with Spatial Query [message #552751] Fri, 27 April 2012 14:48 Go to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Hi,

Iam new to oracle spatial. I need help with following query

I have a table zip_codes
Note: GEOM column is of type SDO_GEOMETRY

ZIPCODE LAT LON GEOM
------- -------- ---------- ---------
24277 36.7573 83.0130 (2001, 8307, (83.013, 36.7573),),,)

( Actual data is in the attached spreadsheet)

Objective: I need to write a query where I should retrieve one zip code value for a specic combination of lat and lon value which falls within that zipcode

i.e for a a lat 36.7573 and lon = 83.0130 it should return 24277

Iam thinking

select zipcode
from zipcodes A
where a.lat = 36.753
and a.lat = 83.0130
and SDO_INSIDE(A.GEOM, A.GEOM)= 'TRUE';


Am I on the right track?

Any help is greatly appreciated.

Thanks
Re: Help with Spatial Query [message #552815 is a reply to message #552751] Sat, 28 April 2012 18:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7861
Registered: November 2002
Location: California, USA
Senior Member
It is unclear what you are trying to do. You seem to have a table where you have latitude and longitude coordinates stored both as individual columns and as points of sdo_geometry type. If you just want to retrieve the zipcode for a specific combination of latitude and longitude, then you do not need a spatial query, as you can just retrieve it from the regular columns, but you can use a spatial query if you like. Sdo_inside is for something different. If you have an area that defines a zip code, then you can search for which of your coordinate points are within that area. I have provided examples of all three below. I provided a couple of queries using sdo_inside, one that searches within a large rectangle and another that searches within a smaller rectangle. The sdo_inside queries search for points in your table that are within the defined area.

-- table and sample data:
SCOTT@orcl_11gR2> CREATE TABLE zip_codes
  2    (zipcode NUMBER,
  3  	lat	NUMBER,
  4  	lon	NUMBER,
  5  	geom	SDO_GEOMETRY)
  6  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO zip_codes VALUES
  2    (24277, 36.7573, 83.013,
  3  	SDO_GEOMETRY
  4  	  (2001,
  5  	   8307,
  6  	   SDO_POINT_TYPE (83.013, 36.7573, NULL),
  7  	   NULL,
  8  	   NULL))
  9  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO zip_codes VALUES
  2    (24282, 36.825, 83.055,
  3  	SDO_GEOMETRY
  4  	  (2001,
  5  	   8307,
  6  	   SDO_POINT_TYPE (83.055, 36.825, NULL),
  7  	   NULL,
  8  	   NULL))
  9  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO zip_codes VALUES
  2    (24282, 36.82825, 83.0501,
  3  	SDO_GEOMETRY
  4  	  (2001,
  5  	   8307,
  6  	   SDO_POINT_TYPE (83.0501, 36.8285, NULL),
  7  	   NULL,
  8  	   NULL))
  9  /

1 row created.

SCOTT@orcl_11gR2> SELECT * FROM zip_codes
  2  /

   ZIPCODE        LAT        LON
---------- ---------- ----------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
     24277    36.7573     83.013
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(83.013, 36.7573, NULL), NULL, NULL)

     24282     36.825     83.055
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(83.055, 36.825, NULL), NULL, NULL)

     24282   36.82825    83.0501
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(83.0501, 36.8285, NULL), NULL, NULL)


3 rows selected.


-- non-spatial query:
SCOTT@orcl_11gR2> SELECT zipcode
  2  FROM   zip_codes
  3  WHERE  lat = 36.7573
  4  AND    lon = 83.0130
  5  /

   ZIPCODE
----------
     24277

1 row selected.


-- spatial query to search for zipcode for exact point:
SCOTT@orcl_11gR2> select a.zipcode
  2  from   zip_codes a
  3  where  a.geom.sdo_point.x = 83.0130
  4  and    a.geom.sdo_point.y = 36.7573
  5  /

   ZIPCODE
----------
     24277

1 row selected.


-- spatial method to search for points within area:
SCOTT@orcl_11gR2> -- insert into user_sdo_geom_metadata (prerequisite for spatial index):
SCOTT@orcl_11gR2> INSERT INTO user_sdo_geom_metadata
  2    (TABLE_NAME,
  3  	COLUMN_NAME,
  4  	DIMINFO,
  5  	SRID)
  6  VALUES
  7    ('zip_codes',
  8  	'geom',
  9  	SDO_DIM_ARRAY
 10  	  ( -- 100 X 100 grid
 11  	   SDO_DIM_ELEMENT
 12  	     ('X',
 13  	      0,       -- lower bound
 14  	      100,     -- upper bound
 15  	      0.005),  -- tolerance
 16  	   SDO_DIM_ELEMENT
 17  	     ('Y',
 18  	      0,       -- lower bound
 19  	      100,     -- upper bound
 20  	      0.005)), -- tolerance
 21  	8307)
 22  /

1 row created.

SCOTT@orcl_11gR2> -- spatial index:
SCOTT@orcl_11gR2> CREATE INDEX zip_codes_spatial_idx
  2  ON zip_codes (geom)
  3  INDEXTYPE IS MDSYS.SPATIAL_INDEX
  4  /

Index created.

SCOTT@orcl_11gR2> -- queries:
SCOTT@orcl_11gR2> -- search for points within large rectangle:
SCOTT@orcl_11gR2> SELECT a.zipcode, a.lat, a.lon
  2  FROM   zip_codes a
  3  WHERE  SDO_INSIDE
  4  	      (a.geom, -- point from the table
  5  	       SDO_GEOMETRY -- area to check if the point is in
  6  		 (2003, 8307, NULL,
  7  		  SDO_ELEM_INFO_ARRAY
  8  		    (1, 1003, 3),     -- rectangle
  9  		  SDO_ORDINATE_ARRAY  -- defined by
 10  		    (83, 36,	      -- lower left corner
 11  		     84, 37)	      -- upper right corner
 12  		 )) = 'TRUE'
 13  /

   ZIPCODE        LAT        LON
---------- ---------- ----------
     24277    36.7573     83.013
     24282     36.825     83.055
     24282   36.82825    83.0501

3 rows selected.

SCOTT@orcl_11gR2> -- search for points within smaller rectangle:
SCOTT@orcl_11gR2> SELECT a.zipcode, a.lat, a.lon
  2  FROM   zip_codes a
  3  WHERE  SDO_INSIDE
  4  	      (a.geom, -- point from the table
  5  	       SDO_GEOMETRY -- area to check if the point is in
  6  		 (2003, 8307, NULL,
  7  		  SDO_ELEM_INFO_ARRAY
  8  		    (1, 1003, 3),	-- rectangle
  9  		  SDO_ORDINATE_ARRAY	-- defined by
 10  		    (83.0129, 36.75729, -- lower left corner
 11  		     83.0131, 36.75731) -- upper right corner
 12  		 )) = 'TRUE'
 13  /

   ZIPCODE        LAT        LON
---------- ---------- ----------
     24277    36.7573     83.013

1 row selected.

Re: Help with Spatial Query [message #552854 is a reply to message #552815] Sun, 29 April 2012 11:34 Go to previous messageGo to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Barbara,

Thank you for your prompt reply, Here is my task.

I have two tableS zipcodes and LOCATION, The data in ZIPCODES looks like this ex.
zipcode     lat    lon
--------    ----   ----
20111       36.12  -80.15
20111       36.15  -80.23
20111       36.56  -80.71
20123       38.98  -81.65
20124       38.99  -81.78

I have another table called LOCATION the data in it looks like this
ID         LAT         LON
-----     ------      ------
1          36.14        - 80.13
2          36.23        - 80.18
3          36.45        - 80.41
4          38.23        - 81.78
5          38.86        - 82.43

Objective: is to write a query to retrieve the zipcodes from zipcodes table for each value in LOCATION table which falls inside the boundary of the zipcode table

i.e let's say if the boundary of the zip code value 20111 ( based on the table data) is LAT is in between 36.12 - 36.56 and LON = -80.15 TO 80.71. So for a value of ID = 1 whose lat = 36.14 and lon = 80.13 the zipcode falls within the boundary of 20111. so it should give me a value of zipcode 20111 when for id = 1.

I need to comeup with a query to determine the zipcode for each value in LOCATION table as to which boundary it falls into.

I hope Iam clear with my question.

Please let me know if it is is still not clear.

Again thank you very much for your help.


[EDITED by LF: merged two messages as the first one was incomplete]

[Updated on: Sun, 29 April 2012 14:32] by Moderator

Report message to a moderator

Re: Help with Spatial Query [message #552860 is a reply to message #552854] Sun, 29 April 2012 13:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7861
Registered: November 2002
Location: California, USA
Senior Member
You need to have enough rows in your zipcodes table for each zipcode to establish enough points per zip code to create an sdo_geometry for each zip code that represents the area that the zip code covers. Any zipcode that does not have enough rows will raise an error or not find the data. If you do have enough rows, then there are various methods that you can use to create such a geometry for each zip code. I have provided a small demonstration below, using three rows from your zipcodes table and one row that I put in your location table. I have included the result of usage of "SDO_AGGR_UNION (MDSYS.SDOAGGRTYPE (MDSYS.SDO_GEOMETRY ..." to show the geometry that is created from the three lines in the zipcodes table for that zip code. Note that I have also added a column to the location table and populated it, based on the values in the other columns and inserted into user_sdo_geom_metadata and create an index to enable usage of SDO_GEOM.RELATE for comparison. If you can provide a larger more realistic data set, with more rows and points in the zipcodes table per zip code, then I may be able to provide a better demonstration.

SCOTT@orcl_11gR2> CREATE TABLE zipcodes
  2    (zipcode  NUMBER,
  3  	lat	 NUMBER,
  4  	lon	 NUMBER)
  5  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO zipcodes VALUES (20111, 36.12, -80.15)
  3  INTO zipcodes VALUES (20111, 36.15, -80.23)
  4  INTO zipcodes VALUES (20111, 36.56, -80.71)
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_11gR2> CREATE TABLE location
  2    (id	 NUMBER,
  3  	lat	 NUMBER,
  4  	lon	 NUMBER)
  5  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO location VALUES (6, 36.15, -80.23)
  3  SELECT * FROM DUAL
  4  /

1 row created.

SCOTT@orcl_11gR2> ALTER TABLE location ADD (geom  SDO_GEOMETRY)
  2  /

Table altered.

SCOTT@orcl_11gR2> UPDATE location l
  2  SET    geom =
  3  	    MDSYS.SDO_GEOMETRY
  4  	      (2001, 8307,
  5  	       SDO_POINT_TYPE
  6  		 (l.lon, l.lat, NULL),
  7  	       NULL, NULL)
  8  /

1 row updated.

SCOTT@orcl_11gR2> INSERT INTO user_sdo_geom_metadata
  2    (table_name, column_name, diminfo, srid)
  3  VALUES
  4    ('location', 'geom',
  5  	SDO_DIM_ARRAY
  6  	  (SDO_DIM_ELEMENT ('X', -100, 100, 0.005),
  7  	   SDO_DIM_ELEMENT ('Y', -100, 100, 0.005)),
  8  	8307)
  9  /

1 row created.

SCOTT@orcl_11gR2> CREATE INDEX location_spatial_idx
  2  ON location (geom)
  3  INDEXTYPE IS MDSYS.SPATIAL_INDEX
  4  /

Index created.

SCOTT@orcl_11gR2> SELECT z.zipcode, l.lat, l.lon, l.geom, z.geom
  2  FROM   (SELECT z.zipcode,
  3  		    SDO_AGGR_UNION
  4  		      (MDSYS.SDOAGGRTYPE
  5  			 (MDSYS.SDO_GEOMETRY
  6  			    (2001, 8307,
  7  			     SDO_POINT_TYPE
  8  			       (z.lon, z.lat, NULL),
  9  			     NULL, NULL),
 10  		       0.005)) geom
 11  	     FROM   zipcodes z
 12  	     GROUP  BY zipcode) z,
 13  	    location l
 14  WHERE  SDO_GEOM.RELATE (l.geom, 'determine', z.geom, 0.005) = 'INSIDE'
 15  /

   ZIPCODE        LAT        LON
---------- ---------- ----------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
     20111      36.15     -80.23
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-80.23, 36.15, NULL), NULL, NULL)
SDO_GEOMETRY(2005, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1, 3), SDO_ORDINATE_ARRAY(
-80.71, 36.56, -80.23, 36.15, -80.15, 36.12))


1 row selected.

Re: Help with Spatial Query [message #552861 is a reply to message #552860] Sun, 29 April 2012 13:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7861
Registered: November 2002
Location: California, USA
Senior Member
Note that you can do what you asked for without spatial, as demonstrated below. However, this could give wrong results if the zip code areas are not rectangular areas, so that any latitude and longitude between the minimum and maximum are within the zip code area.

SCOTT@orcl_11gR2> CREATE TABLE zipcodes
  2    (zipcode  NUMBER,
  3  	lat	 NUMBER,
  4  	lon	 NUMBER)
  5  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO zipcodes VALUES (20111, 36.12, -80.15)
  3  INTO zipcodes VALUES (20111, 36.15, -80.23)
  4  INTO zipcodes VALUES (20111, 36.56, -80.71)
  5  INTO zipcodes VALUES (20123, 38.98, -81.65)
  6  INTO zipcodes VALUES (20124, 38.99, -81.78)
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl_11gR2> CREATE TABLE location
  2    (id	 NUMBER,
  3  	lat	 NUMBER,
  4  	lon	 NUMBER)
  5  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO location VALUES (1, 36.14, -80.13)
  3  INTO location VALUES (2, 36.23, -80.18)
  4  INTO location VALUES (3, 36.45, -80.41)
  5  INTO location VALUES (4, 38.23, -81.78)
  6  INTO location VALUES (5, 38.86, -82.43)
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl_11gR2> SELECT z.zipcode, l.lat, l.lon, l.id
  2  FROM   (SELECT zipcode,
  3  		    MIN (lat) minlat, MAX (lat) maxlat,
  4  		    MIN (lon) minlon, MAX (lon) maxlon
  5  	     FROM   zipcodes
  6  	     GROUP  BY zipcode) z,
  7  	    location l
  8  WHERE  l.lat BETWEEN z.minlat AND z.maxlat
  9  AND    l.lon BETWEEN z.minlon AND z.maxlon
 10  /

   ZIPCODE        LAT        LON         ID
---------- ---------- ---------- ----------
     20111      36.23     -80.18          2
     20111      36.45     -80.41          3

2 rows selected.

[Updated on: Sun, 29 April 2012 14:19]

Report message to a moderator

Re: Help with Spatial Query [message #552863 is a reply to message #552861] Sun, 29 April 2012 14:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7861
Registered: November 2002
Location: California, USA
Senior Member
Here is another spatial example that uses the maximum and minimum points that you described in the zipcodes table for one zip code to show that, using SDO_GEOM.SDO_CONCAVEHULL, it can create a proper SDO_GEOMETRY object to tell if the points in the location table are inside the zip code area. This is what I believe you are trying to do.

SCOTT@orcl_11gR2> CREATE TABLE zipcodes
  2    (zipcode  NUMBER,
  3  	lat	 NUMBER,
  4  	lon	 NUMBER)
  5  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO zipcodes VALUES (20111, 36.12, -80.15)
  3  INTO zipcodes VALUES (20111, 36.56, -80.71)
  4  INTO zipcodes VALUES (20111, 36.12, -80.71)
  5  INTO zipcodes VALUES (20111, 36.56, -80.15)
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl_11gR2> CREATE TABLE location
  2    (id	 NUMBER,
  3  	lat	 NUMBER,
  4  	lon	 NUMBER)
  5  /

Table created.

SCOTT@orcl_11gR2> INSERT ALL
  2  INTO location VALUES (1, 36.14, -80.13)
  3  INTO location VALUES (2, 36.23, -80.18)
  4  INTO location VALUES (3, 36.45, -80.41)
  5  INTO location VALUES (4, 38.23, -81.78)
  6  INTO location VALUES (5, 38.86, -82.43)
  7  SELECT * FROM DUAL
  8  /

5 rows created.

SCOTT@orcl_11gR2> ALTER TABLE location ADD (geom  SDO_GEOMETRY)
  2  /

Table altered.

SCOTT@orcl_11gR2> UPDATE location l
  2  SET    geom =
  3  	    MDSYS.SDO_GEOMETRY
  4  	      (2001, 8307,
  5  	       SDO_POINT_TYPE
  6  		 (l.lon, l.lat, NULL),
  7  	       NULL, NULL)
  8  /

5 rows updated.

SCOTT@orcl_11gR2> INSERT INTO user_sdo_geom_metadata
  2    (table_name, column_name, diminfo, srid)
  3  VALUES
  4    ('location', 'geom',
  5  	SDO_DIM_ARRAY
  6  	  (SDO_DIM_ELEMENT ('X', -100, 100, 0.005),
  7  	   SDO_DIM_ELEMENT ('Y', -100, 100, 0.005)),
  8  	8307)
  9  /

1 row created.

SCOTT@orcl_11gR2> CREATE INDEX location_spatial_idx
  2  ON location (geom)
  3  INDEXTYPE IS MDSYS.SPATIAL_INDEX
  4  /

Index created.

SCOTT@orcl_11gR2> SELECT z.zipcode, l.lat, l.lon, l.geom, z.geom
  2  FROM   (SELECT z.zipcode,
  3  		    SDO_GEOM.SDO_CONCAVEHULL
  4  		      (SDO_AGGR_UNION
  5  			 (MDSYS.SDOAGGRTYPE
  6  			    (MDSYS.SDO_GEOMETRY
  7  			       (2001, 8307,
  8  				SDO_POINT_TYPE
  9  				  (z.lon, z.lat, NULL),
 10  				NULL, NULL),
 11  			  0.005)),
 12  		       0.005) geom
 13  	     FROM   zipcodes z
 14  	     GROUP  BY zipcode) z,
 15  	    location l
 16  WHERE  SDO_INSIDE (l.geom, z.geom) = 'TRUE'
 17  /

   ZIPCODE        LAT        LON
---------- ---------- ----------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
     20111      36.23     -80.18
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-80.18, 36.23, NULL), NULL, NULL)
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-80.71, 36.12, -80.15, 36.12, -80.15, 36.56, -80.71, 36.56, -80.71, 36.12))

     20111      36.45     -80.41
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-80.41, 36.45, NULL), NULL, NULL)
SDO_GEOMETRY(2003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-80.71, 36.12, -80.15, 36.12, -80.15, 36.56, -80.71, 36.56, -80.71, 36.12))


2 rows selected.



[Updated on: Sun, 29 April 2012 14:23]

Report message to a moderator

Re: Help with Spatial Query [message #552967 is a reply to message #552863] Mon, 30 April 2012 10:21 Go to previous messageGo to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Barbara,

Thank you very much for providing multiple options.

I think the cancaehull is the right approach but when I ran the query on my data( which has lot more data)

it gave my the following error. Any insight into this error is greatly appreciated.

Again Iam new to oracle spatial Smile

ORA-13198: Spatial error: "code 15 in mdtnmcrt - too few points for TIN"
ORA-06512: at "MDSYS.SDO_GEOM", line 2294
13198. 00000 - "Spatial error: %s"
*Cause: Internal error in some Oracle Spatial stored procedure.
*Action: Record the sequence of procedure calls or events that
preceded this error, and contact Oracle Support Services if
the error message text does not clearly specify the cause
of the error.

Thanks
Re: Help with Spatial Query [message #552972 is a reply to message #552863] Mon, 30 April 2012 10:51 Go to previous messageGo to next message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Barbara,

Attached is my zipcodes file.
Re: Help with Spatial Query [message #552979 is a reply to message #552967] Mon, 30 April 2012 11:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7861
Registered: November 2002
Location: California, USA
Senior Member
The error message means that some of your zip codes do not have enough points to establish an area.
Re: Help with Spatial Query [message #552981 is a reply to message #552979] Mon, 30 April 2012 11:59 Go to previous message
gentleman777us
Messages: 117
Registered: April 2005
Senior Member
Thanks!
Previous Topic: cant start oracleDBconsoleorcl service on my xp machine
Next Topic: How to convert oracle table data to shape file
Goto Forum:
  


Current Time: Thu Apr 24 10:29:21 CDT 2014

Total time taken to generate the page: 0.13128 seconds