How can I query a geometry field in PostGIS for line segments within a given lat/long bounding box?

Question

In google maps, I want to use map.getBounds() and take those values and query my PostGIS instance for all line segments that intersect that range. I am doing something wrong. I tried:

SELECT * FROM pipeline_denorm
WHERE pipeline_denorm.wkb_geometry &&
ST_Transform(
    ST_MakeEnvelope(
        -121.76986352563478, 37.85809027719253, 
        -121.19308129907228, 38.07462286323802, 4326), 
    2223);

based on a little research. Unfortunately no results are being returned and I am certain there is data that should be being returned. These are LineStrings. Basically want any record that contains a point within the bounding box.

Double check the SRID of your geometry: SELECT ST_SRID(pipeline_denorm.wkb_geometry) FROM pipeline_denorm LIMIT 1; or look at the column type in pgAdmin — kttii, Nov 08 '16 at 17:35

score 2 · Answer 1 · edited Apr 13 '17 at 12:33

It looks like you copied the SQL from here, but it is not clear that you need the ST_Transform to SRID of 2223. Use the SQL @kttii included in a comment, and replace the 2223 with that number.

The way you have it written with the && operator, you are comparing bounding boxes rather than the line itself. This may give you unexpected results if a line curves around a lot. I suspect you would prefer ST_Intersects, so you would have something like this:

SELECT * 
FROM pipeline_denorm
WHERE 
  ST_Intersects(pipeline_denorm.wkb_geometry,
     ST_MakeEnvelope(-121.76986352563478, 
                      37.85809027719253, 
                     -121.19308129907228, 
                      38.07462286323802, 
                      4326
                    )
                );

You only need to add the ST_Transform if the SRID of the wkb_geometry column is different than 4326.

How can I query a geometry field in PostGIS for line segments within a given lat/long bounding box?

1 Answers1