5 Replies Latest reply: Mar 9, 2014 2:35 PM by John O'Toole RSS

    Point inside polygons

    NathanT

      I am trying to figure out a way to get the ids for all the polygons under which a common point falls. Eg if a city(point) falls under a county,state and country I would like pass the co-ordinates for the city and get back the names of the polygons.Any suggestions how to go about it.

        • 1. Re: Point inside polygons
          John O'Toole

          I would use an sdo_inside query to find the polygons that the point falls within. 
          E.g.

          select <polygon name>
          from <polygon table>
          where sdo_inside(<polygon geometry column>, <point geometry>) = 'TRUE';
          

           

          If you let us know the structure of your tables then I can provide a more precise example.  For example, are the county, state and country polygons all in one table, or a table per type?

           

          John

          • 2. Re: Point inside polygons
            NathanT

            Each polygon layer has a different table and that's where my trouble started.

            • 3. Re: Point inside polygons
              John O'Toole

              Run a separate query for each and union them all together.

              select 'City' as type, name
              from city_table  
              where sdo_inside(<polygon geometry column>, <point geometry>) = 'TRUE'
              union all
              select 'County' as type, name
              from county_table  
              where sdo_inside(<polygon geometry column>, <point geometry>) = 'TRUE'
              union all
              select 'State' as type, name
              from state_table  
              where sdo_inside(<polygon geometry column>, <point geometry>) = 'TRUE'
              union all
              select 'Country' as type, name
              from country_table  
              where sdo_inside(<polygon geometry column>, <point geometry>) = 'TRUE'
              
              • 4. Re: Point inside polygons
                NathanT

                In case of Union you need all the fields in all the tables to be matched. What in case if they dont.

                • 5. Re: Point inside polygons
                  John O'Toole

                  > What in case if they dont.
                  Then run separate queries.

                   

                  Your original requirement was to "figure out a way to get the ids for all the polygons under which a common point falls".  So I assume that you only want to select one column from each table.  If the column names don't match, then use an alias as I did in the example above.