How to SELECT all points within a GeoJSON polygon in PostGIS

Question

I have a GeoJSON polygon, and a table with lots of points. I want to SELECT all points that are within an arbitrary GeoJSON polygon.

How can I do that with PostGIS SQL?

Answer 1

You need a ST_GeomFromGeoJSON

select * 
from points p
where ST_Within(
    p.geom,
    ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[...]}')
)

Edit: ST_Intersects works also (need to set SRID on GeoJSON, and transform):

select row_to_json(t) 
from (select * from table 
where st_intersects(
    st_transform(
        st_setsrid(
            ST_geomfromgeojson('{"type":"Polygon","coordinates":[...]}'),
        4326), 
    3857), 
table.geom)) as t;