[GIS] Getting a Bounding Box in Latitude / Longitude using PostGIS shp2pgsql

coordinate systemextentspostgis

Using the PostGIS shp2pgsql -s 4326 … I generated the sql file and created my spatially enabled table.

When executing my query:

select box2d(ST_extent(the_geom)) from feature_data;

I'm returned with:

BOX(490755.1875 5460528,504640.4375 5466997)

My result appears to be in Easting and Northing, but I would like to return the bounding box in latitude / longitude. I have limited experience with GIS. How can I convert this result to the latitude / longitude coordinate system?

Best Answer

Looks like your shapefile uses the following projection: http://spatialreference.org/ref/sr-org/6855/

At least this looks like the content of the .prj file you posted: http://spatialreference.org/ref/sr-org/6855/html/

To add this reference system to PostGIS, you can run: http://spatialreference.org/ref/sr-org/6855/postgis/

The correct query then would be:

SELECT box2d(ST_Transform(ST_SetSRID(the_geom,96855),4326)) FROM feature_data;

Related Solutions

[GIS] Shortest path postgis query with in a bounding box

Since there is no data it's difficult to provide some ready-to-use solution, but:

Check if your data is in EPSG:3857 - when good query with some spatial conditions is not returning records in 99% cases it's projection. Table names suggests you're using osm2po4pgr and it's default projection is 4326 (as I see in my data)
Check your sub-query if it's returning records. If yes then trouble is with routing, if no it's about sub-query (e.g. projection from p.1)
Don't use envelope on points of start and end - use some buffer around because route doesn't have to go straight from point A to B - sometimes roads forces driving opposite direction for some time.
If you're searching for performance improvement try to search in union of 3 datasets - all roads in buffer 1km from start and end point + only main roads in envelope of this buffers
Last but not least - of course, you read all materials about improving performance of pg_routing queries and you made all needed indexes? If I remember routing itself needs source and target fields, if you're using && some spatial index could be useful, if you'll use my sugestions from point 4 you also need index on clazz column.

[GIS] PostGIS – Bounding Box

In PostGIS you can store the coordinate pairs to a table that has a column with the polygon geometry type and then use the ST_Intersects function.

For example, you can create a table data and insert the bounding box in your example like this:

CREATE TABLE data(id serial PRIMARY KEY NOT NULL, geom geometry(Polygon, 4326));
INSERT INTO data(geom) VALUES(ST_GeomFromText('POLYGON((16.23 48.123, 16.23 49.012, 16.367 49.012, 16.367 48.123, 16.23 48.123))', 4326));

The column named geom has the polygon type and SRID 4326. and you can insert the bounding boxes by using the ST_GeomFromText function that accepts the Well-known text (WKT) type geometries.

After you have inserted all the bounding boxes, you can use the following query to find the intersecting boxes and their ids:

SELECT id, ST_Intersects(ST_GeomFromText('POINT(16.5 48.5)',4326), geom) FROM data;

Best Answer

Related Solutions

[GIS] Shortest path postgis query with in a bounding box

[GIS] PostGIS – Bounding Box

Related Question