[postgis-users] Postgresql, postgis and PHP

[pcreso at pcreso.com]

Hi,

You can use simple SQL to extract core values which you manipulate & analyse in PHP/Javascript, or you can do more of the work in SQL. Given your skills, it seems likely that you would find it easier to use simple SQL & more script based processing.

One initial point to note: 

If you are dealing with global polygons, then SRID 4326 based data can give very strange results for area calculations. eg, the polygons:

(0 0,  1 0,  1 1,  0 1,  0 1 ) and
(0 90, 1 90, 1 89, 0 89, 0 90)

are both 1 degree square, and will give you the same area when queried. However, these are cartesian degrees, and you want the spherical area, which will be close to 1 degree at the equator, but a small fraction of this at the pole.

So, you need to either convert (transform) your coordinates to a suitable  coordinate system (identified by its SRID) before querying the area, or deal with this issue some other way, but you need to deal with it.

Then you can use the Postgis SQL functions to retrieve the desired area values of the transformed polygon geometries. The Postgis docs give you good basic instructions on the commands, but tend to assume you already understand such issues.


Rather than write a Postgis guide here, I recommend you buy a copy of "Postgis is Action" (http://www.manning.com/obe/) - you can download the digital version prior to the printed edition being available. 

Otherwise a full discussion on this list could virtually replicate the contents of the book! 


Cheers,

  Brent Wood


--- On Wed, 2/2/11, Louwrens du Toit <louwrence_du at eva.mpg.de> wrote:

> From: Louwrens du Toit <louwrence_du at eva.mpg.de>
> Subject: [postgis-users] Postgresql, postgis and PHP
> To: postgis-users at postgis.refractions.net
> Date: Wednesday, February 2, 2011, 11:24 PM
> Dear Postgis-Users List,
> 
> I'm an experienced web developer (mainly PHP, Javascript,
> MySQL) and have recently started a job where I need work
> with geographic information, mainly in a Postgresql
> database. So, I'm quite new to all this geographical data
> etc and have a few questions:
> 
> So, I have a database (Postgresql) with geographical data
> (mainly polygons in format "geometry" - srid 4326). Now I
> can do some area (ST_Area) and intersection
> (ST_Intersection) queries but would like to know the
> following:
> 
> 1. I have several tables with polygon data (layers) but
> need to do queries across them (what I need to do in the end
> is to create charts - bar,pie etc). I.e I would like to know
> for instance, what percentage area of layer 1 falls within
> the area in layer 2? or
> 2. I have raster data (in geotiff format), and would like
> to know... per polygon (in layer 1 above), what is the mean
> value of the raster data...
> 
> Can someone point me in a direction of where to information
> like this. How should I go about this problem?
> 
> I hope this makes sense. As I said, I'm very new to all
> this geographic stuff.
> 
> Thank you,
> LJ du Toit
> 
> -- 
> __________________________________________________
> LJ du Toit
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>