Re: Point-in-polygon spatial queries

Hi Vince,

> 1)Let me know if the two tables above, along with a point in space,
> give me everything I need to write my query, and

First of all, the second table you mention (Spatial_table_SX_HL12N4$) is really the spatial index table. Do not touch it - actually, forget it is there at all. That table was automatically built and populated when you created the spatial index (using the CREATE INDEX ... INDEXTYPE IS MDSYS.SPATIAL_INDEX ... command). If you used Mapinfo's Easyloader to load the data (as it seems you did, from the look of your data table), then Easyloader issued this statement for you.

> 2)send me a query that returns the NPA, NXX, and NUMB columns for all
> rows in the spatial_table whose polygons contain the point in space
> listed above?

Then the query you want will be like this:

SELECT NPA, NXX, NUMB
  FROM SPATIAL_TABLE
 WHERE MDSYS.SDO_RELATE (

          GEOLOC, 
          MDSYS.SDO_GEOMETRY (1, NULL, 
                              MDSYS.SDO_POINT_TYPE (87.654321,
12.345678, NULL),
                              NULL, NULL),
          'mask=CONTAINS') = TRUE;

Is this obscure enough ? ;-).

The MDSYS.SDO_GEOMETRY(...) notation is the constructor of the geometry object. Each argument corresponds to an attribute of the object:

    1 = the SDO_GTYPE attribute. 1 indicates this is a point     NULL = the SDO_SRID attribute, unused in 8.1.5     MDSYS.SDO_POINT_TYPE() = the actual point. Its arguments are the X, Y and Z values for the point.

    NULL = the SDO_ELEM_INFO attribute: unused for a single point     NULL = the SDO_ORDINATES attribute: unused for points too.

The parameter string that follows ('mask=CONTAINS') defines the kind of spatial relationship that we want tested. In this case, we want to get all the polygons that CONTAIN the chosen point.

> 3)Give me the title of a good book that covers the Spatial Cartridge
> and Spatial queries in depth?

The Oracle Spatial User's Guide ?

Just kidding. I don't know of any such book - and I doubt that any exists. However, we are aware of the shortcomings of the current manual and are busy updating and improving it for 8.1.6.

I hope this helps

/albert

--

Albert Godfrind         Oracle Data Server Division
Oracle Corporation      Multimedia and GeoSpatial Technologies
C.I.C.A                 Email:  agodfrin_at_fr.oracle.com    
2229 Route des Crêtes   Phone:  +33/4/92.94.21.37    
06560 Sophia-Antipolis  Mobile: +33/6/09.97.27.23    
France                  FAX:    +33/4/92.94.21.45    
                        http://www.oracle.com

vmisetich_at_my-deja.com wrote:
>
> Hello!
> I've got Oracle 8i EE installed on a Win NT 4.0 server. I have some
> questions about writing spatial queries (I'm brand new to the Oracle
> Spatial Cartridge). My company has purchased a ton of data from
> MapInfo and loaded several MapInfo tables into our Oracle db. We're at
> a point now where we're trying to figure out how to query the data
> we've loaded. I need a query that will, when fed a latitude and
> longitude, find all polygons in a spatial table that contain that
> point. I've looked at the Oracle 8i documentation, and it says that
> it's possible to do such a query, but I'm having trouble pulling the
> query I need from the doc's examples. For example, suppose I have a
> point in space represented by latitude 12.345678 and longitude
> 87.654321, and suppose I have two tables (one spatial table and its
> associated index table) with the following structures:
> Table 1: "Spatial_table". Structure:
> NPA VARCHAR2(10) NULL,
> NXX VARCHAR2(10) NULL,
> NUMB VARCHAR2(10) NULL,
> PRINX NUMBER NULL,
> GEOLOC MDSYS.SDO_GEOMETRY NULL
>
> Table 2: "Spatial_table_SX_HL12N4$". Structure:
> SDO_GROUPCODE RAW(14) NULL,
> SDO_ROWID ROWID NULL,
> SDO_CODE RAW(14) NULL,
> SDO_META RAW(14) NULL,
> SDO_MAXCODE RAW(14) NULL
>
> Could someone please:
> 1)Let me know if the two tables above, along with a point in space,
> give me everything I need to write my query, and
> 2)send me a query that returns the NPA, NXX, and NUMB columns for all
> rows in the spatial_table whose polygons contain the point in space
> listed above?
> 3)Give me the title of a good book that covers the Spatial Cartridge
> and Spatial queries in depth?
> (In case you're wondering, our MapInfo representative is swamped and
> won't be able to help us for at least a few weeks, which is more time
> to wait than we have.)
>
> Thanks in advance for any help you can provide. And sorry about the
> terribly long post.
>
> Thanks,
> Vince

Received on Fri Nov 26 1999 - 08:21:43 CST