2

I have the following query:

SELECT row_to_json(fc)
FROM (
    SELECT ST_AsGeoJSON(lg.geometry)::json As geometry, 
            (
                select row_to_json(t) 
                from (
                    select name, type, admin_level
                ) t
            )
        As properties

    FROM osm_admin As lg 
) 

As fc;

This returns all data in table as GeoJSON.

How can I additionally restrict the results to only within the bounding box? (As the second image below.)

I need to get full GeoJSON for a tile. The PostGIS data is probably in 3857, as it's OSM imported as pbf with imposm3.

enter image description here


Have tried this, as per Mark C's suggestion:

SELECT row_to_json(fc) 
    FROM (

        SELECT ST_AsGeoJSON(lg.geometry)::json As geometry, 

        (SELECT row_to_json(t) 
            FROM (SELECT name, type, admin_level)
            WHERE the_geom && ST_SetSRID(ST_MakeBox2D(ST_Point(-73.9980, 40.726), ST_Point(-73.995, 40.723)), 4326) 
            As t 
        )

        As properties

        FROM osm_admin As lg 
    ) 
As fc;

But I get the following error:

ERROR:  subquery in FROM must have an alias
LINE 6: FROM (SELECT name, type, admin_level)

2 Answers 2

2

Assuming that you want the actual intersection of the bounding box, then create a bounding box as a sub-query at the same level as the select from osm_admin, and take the intersection of this bbox with lg.geometry. Note the ST_Intersects also in the where clause.

SELECT row_to_json(fc)
  FROM (
    SELECT ST_AsGeoJSON(ST_Intersection(lg.geometry, bbox.geom))::json As geometry, 
            (
               SELECT row_to_json(t) 
                 FROM (
                      SELECT name, type, admin_level
                 ) t
            )
     As properties
  FROM 
      osm_admin As lg, 
      (SELECT ST_SetSRID(ST_MakeBox2d(ST_MakePoint(x1,y1),
                ST_MakePoint(x2,y2)), 3857) as geom) as bbox 
  WHERE ST_Intersects(lg.geom, bbox.geom)
) fc;
5
  • No errors, but doesn't return any data either?
    – knutole
    Commented Jan 12, 2015 at 15:00
  • 1
    Actually, had some issues with coordinates. Seems to work swell! Thanks a lot!
    – knutole
    Commented Jan 12, 2015 at 15:25
  • 1
    Great. There are other (shorter) ways of making a bounding box, of course, I just like the ST_MakeBox2d and ST_MakePoint approach. Commented Jan 12, 2015 at 15:33
  • Works great. If I wanted to add a ST_Transform in there, to use lat/lon instead of meters, could I do that?
    – knutole
    Commented Jan 12, 2015 at 15:38
  • 1
    Do you mean ST_Transform of the output? If, so, yes, as the osm and bbox will both be in 3587, you can just change to ST_AsGeoJSON(ST_Transfrom(ST_Intersection(lg.geometry, bbox.geom), 4326))::json Commented Jan 12, 2015 at 15:45
1

Form Here! it says ..

SELECT * FROM {table_name} WHERE the_geom && ST_SetSRID(ST_MakeBox2D(ST_Point(-73.9980, 40.726), ST_Point(-73.995, 40.723)), 4326)

Here, we use some of the same functions you have seen previously. The opperator, &&, always uses the bounding box of geometries, unlike ST_Intersects which uses the full shape of a geometry. This can greatly speed up your queries in cases where bounding box comparison is sufficient.

Untested, but give this a shot ..

SELECT row_to_json(fc)
FROM (
    SELECT ST_AsGeoJSON(lg.geometry)::json As geometry, 
        (
        SELECT name, type, admin_level, geometry FROM osm_admin As lg WHERE the_geom
          && ST_SetSRID(ST_MakeBox2D(ST_Point(-73.9980, 40.726), ST_Point(-73.995, 
          40.723)), 4326)
        ) t
    )
) As fc;

You will need to add in your bonding box corners and may need to change the SRID.

6
  • Thanks for your input. See my edit. Tried your untested query also, but same error. How come the ST_Intersects syntax is not used? Thanks again!
    – knutole
    Commented Jan 12, 2015 at 14:29
  • 1
    I think I messed up the sub-query. I do not have a database free to test this on right now. Hopefully someone will comment with the right syntax, if not, I will have a Db free tomorrow and will correct it. Commented Jan 12, 2015 at 14:32
  • 1
    According to the CartoDb docs, the BB query is a lot faster than the Intersects. Commented Jan 12, 2015 at 14:34
  • I added the WHERE clause inside the FROM parenthesis', which worked. But I think it's returning too many results.
    – knutole
    Commented Jan 12, 2015 at 14:34
  • 1
    Figured it out with the other answer. Thanks for your help!
    – knutole
    Commented Jan 12, 2015 at 15:26

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.