1

We have a property search site where the user draws a polygon on a map and then the app displays all properties that fall within the polygon.

We have properties stored within a Postgres table that included the longitude and latitude values for each individual property (ie: Property.Id, Property.Longitude, Property.Latitude)

Example of the points of the polygon drawn by the user:

-83.39258193445859 42.5188864690573, -83.3550447440939 42.51981388191728, -83.34222679201048 42.50184518501131, -83.38033561769407 42.49526380665137, -83.41707115236205 42.505135614429214, -83.39155196619687 42.51896997517796, -83.39258193445859 42.5188864690573

How would I write a Postgres/PostGIS query that finds all records in my property table where the long/lat fall within the polygon coordinates displayed above?

  • You want PostGIS, the geography data type, and ST_Within. – Craig Ringer Jan 25 '14 at 4:05
4

It doesn't sound like you are storing this data in a spatially enabled database. You might benefit greatly from doing this.

Instead of storing the latitude and longitude separately, you could store them in a geometry column. Then, you can use the st_within function to do the query for you.

  • Used the answer above to solve my problem, but I can't post my answer until tomorrow as I'm a new user to the forum. – LeeVDuhl Jan 24 '14 at 18:32
0

Solution found.

Using the recommendation above, I added "Geometry" based column to the table and populated it using the ST_MakePoint function as shown below:

UPDATE "Property"
Set "Geom" = ST_SetSRID(
    ST_MakePoint("Property"."Longitude", "Property"."Latitude"), 
    3078);

I then used the ST_Within function to see if the Geometry based points falls within my custom polygon using the ST_GeomFromText function and my closed polygon points.

SELECT 
    "Property"."MLSNumber", 
    "Property"."Longitude", 
    "Property"."Latitude"
FROM "Property"
WHERE ST_Within("Property"."Geom", 
    ST_GeomFromText(
        'POLYGON(-83.39258193445859 42.5188864690573, 
        -83.3550447440939 42.51981388191728, 
        -83.34222679201048 42.50184518501131, 
        -83.38033561769407 42.49526380665137,
        -83.39258193445859 42.5188864690573)'
    )
)

The result "appears" to be correct but I have not yet plotted the polygon and returned points on a map to confirm it.

  • 2
    -1 for following the instructions from notkilroy and posting this as own answer. Even if your answer is more detailed. – Stefan Jan 27 '14 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.