1

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

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;
  • No errors, but doesn't return any data either? – knutole Jan 12 '15 at 15:00
  • 1
    Actually, had some issues with coordinates. Seems to work swell! Thanks a lot! – knutole Jan 12 '15 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. – John Powell Jan 12 '15 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 Jan 12 '15 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 – John Powell Jan 12 '15 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.

  • 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 Jan 12 '15 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. – Mark Cupitt Jan 12 '15 at 14:32
  • 1
    According to the CartoDb docs, the BB query is a lot faster than the Intersects. – Mark Cupitt Jan 12 '15 at 14:34
  • I added the WHERE clause inside the FROM parenthesis', which worked. But I think it's returning too many results. – knutole Jan 12 '15 at 14:34
  • 1
    Figured it out with the other answer. Thanks for your help! – knutole Jan 12 '15 at 15:26

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.