Home » Server Options » Spatial » Help with Spatial Query (oracle 11g)
| Help with Spatial Query [message #552751] |
Fri, 27 April 2012 14:48  |
gentleman777us
Messages: 122
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  |
 |
Barbara Boehmer
Messages: 9100
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 |
gentleman777us
Messages: 122
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 |
|
Barbara Boehmer
Messages: 9100
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 |
|
Barbara Boehmer
Messages: 9100
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 |
|
Barbara Boehmer
Messages: 9100
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 |
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 23 02:11:55 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
