PostGIS SQL syntax for bounding box

Question

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.

---

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)

Answer 1

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;

Answer 2

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.