2

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 8 '16 at 17:35
2

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.

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.