Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Unanswered: Help required with PostGres Spatial Query

    Hi, we have a number of records stored in a database that represent polygons to do with mapping. We are using the Geometry datatype and have inserted the relevant longitude / latitude data into the database.

    The issue I am having is when I run the following query

    Code:
    WHERE ST_Contains(location, ST_GeomFromEWKT('SRID=4326;POINT(138.428092 -31.888547)')) is true
    I can get back more then one result as it's possible for a the longitude / latitude to exist in one or more polygons.

    At it's most basic we have one row that contains a boundary for a country another row that contains a boundary for a state and then another row that contains the boundary for a city.

    If I run the above query I get back the result for the country, state and city.

    I want to be able to get back the smallest possible result (in this case the city).

    Thanks for any help/ ideas
    Regards
    Mark

  2. #2
    Join Date
    Sep 2010
    Posts
    2
    I assume that they are closed polygons with an area?
    If so add an order by area(...) ascending function and a limit clause to 1


    select .... from .. where .... order by area( ...) asc limit 1;

  3. #3
    Join Date
    Sep 2010
    Posts
    3
    Thanks for your help
    Regards
    Mark

  4. #4
    Join Date
    Sep 2010
    Posts
    3

    Outer boundary of polygons / multipolygons in a result set

    Hi, I'm not sure if this is possible but given a result set that contains a list of geometry types (These types contain both single and multipolygon) is it possible to get the outer most boundary and create a polygon from these items?

    We have a list of polygons that are child polygons of a single polygon (Once again, we have a one to many cities that make up a state. I would like to build a new polygon at state level given all the cities (city boundaries) that make up a state.

    Thanks again for any help
    Regards
    Mark

  5. #5
    Join Date
    Oct 2010
    Posts
    1
    I accept with information:the Mascot will be there for sure next week. Unless it gets caught up in some Olympic boycott or anti-Chinese protest. That's a promise.


    __________________
    ps3 game download
    how to download ps3 games

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Have you looked into the PostGIS module?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •