0

I am editing a shapefile as wide as an island. The data are stored in a Postgres/PostGIS database. I want to display only features from my defined bounding box. The bounding box itself is a custom layer that i made myself and it is not stored in the database.

How can this be achieved?

Update: the PostGIS manual has this particular example

SELECT
  m.name,
  sum(ST_Length(r.the_geom))/1000 as roads_km
FROM
  bc_roads AS r,
  bc_municipality AS m
WHERE
  ST_Contains(m.the_geom,r.the_geom)
GROUP BY m.name
ORDER BY roads_km;

Since I dont have the bounding box geometry in the database, is there another way to mimic the bounding box geometry? It would be something like this

SELECT
 layer,
 (x1,y1), (x1,y2), (x2,y2), (x2, y1) AS bbox
FROM
 layer
WHERE
 ST_Contains(layer.the_geom, bbox);
2

You don't need to store your query extent in the database, but the best option is still to make a geometry type from it. I would recommend making a WKT represenation of your geometry by whatever means you deem appropriate, then just use ST_GeomFromText in a query, e.g.:

-- wkt is a WKT representation of your box
SELECT *
FROM bc_roads
WHERE bc_roads.geom && ST_GeomFromText(wkt);
|improve this answer|||||

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.