2

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?

2
  • 1
    What do you mean with GeoJSON polygon? Do you have that saved into a file on your hard disk or what?
    – user30184
    Commented Aug 3, 2015 at 11:25
  • I have it as a JSON object.
    – knutole
    Commented Aug 3, 2015 at 11:27

1 Answer 1

7

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;
2
  • Ansewer is on point :)... But you may be interrested to know there is a JavaScript library for processing Geojson turf.js - github.com/Turfjs/turf. If you are considering for a web based implementation
    – user33290
    Commented Aug 3, 2015 at 12:47
  • How can I do a subquery on this also? ie. instead of FROM select * from table, I want to do FROM (select * from table where height>1500) as sub - but this doesn't work. Why?
    – knutole
    Commented Aug 3, 2015 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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