Showing posts with label postgis. Show all posts
Showing posts with label postgis. Show all posts

Friday, February 5, 2010

Fun with postgis

Recently we had to calculate the bounding boxes for the states. So we planned the activity with postgis.

Here is a SQL script to calculate MINX,MINY,MAXX,MAXY.

select "STATE_NAME" AS statename,
xmin(ST_EXTENT(ST_Envelope(wkb_geometry))) as MINX_26985,
ymin(ST_EXTENT(ST_Envelope(wkb_geometry))) as MINY_26985, xmax(ST_EXTENT(ST_Envelope(wkb_geometry))) as MAXX_26985,
ymax(ST_EXTENT(ST_Envelope(wkb_geometry))) as MAXY_26985,
xmin(ST_Transform(ST_GeomFromText(ASTEXT(ST_EXTENT(ST_Envelope(wkb_geometry))),26985),4326)) as XMIN_4326,
ymin(ST_Transform(ST_GeomFromText(ASTEXT(ST_EXTENT(ST_Envelope(wkb_geometry))),26985),4326)) as MINY_4326,
xmax(ST_Transform(ST_GeomFromText(ASTEXT(ST_EXTENT(ST_Envelope(wkb_geometry))),26985),4326)) as MAXX_4326,
ymax(ST_Transform(ST_GeomFromText(ASTEXT(ST_EXTENT(ST_Envelope(wkb_geometry))),26985),4326)) as MAXY_4326 from "states" group by "STATE_NAME"


Here are, how the results look like at PG-ADMIN GUI.




Last step is to verify, what we have done in the same environment.

I verified with quantum gis(Extension: PG Query for QGIS) for one of the bounding box "POLYGON((-197183.109375 -124668.3828125,-197183.109375 200596.6875,554324.75 200596.6875,554324.75 -124668.3828125,-197183.109375 -124668.3828125))" and here is how it looks like,