Select points from a table that exist in a custom polygon

Question

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?

Answer 1

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.

Answer 2

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.