Query Postgis Table with PHP

Question

Would you be so kind as to help me with the following in OpenLayers. I need to be able to use the coordinates from a user click in a postgis query. To catch the coordinates of a click I built a click handler to use the function map.getLonLatFromViewPort(ext.xy), which delivers me the coordinates of my click in the following format: lon = 568417.33622114, lat = 681985.1902844. These are sent to a php-script using a GET request.

I then want to query my postgis db using ST_GeometryFromText, which requires that I put my coordinates in the following syntax ((568417.33622114 681985.1902844)).

Any help on how to get my coordinates in the correct format would be appreciated!

Answer 1

if you dont want to write your php bridge with postgis, you can use PostGIS to GeoJSON file from postgis_geojson.php github from Bryan McBride.

/**
* PostGIS to GeoJSON
* Query a PostGIS table or view and return the results in GeoJSON format, suitable for
 use in OpenLayers, Leaflet, etc.
*
* @param string $geotable The PostGIS layer name *REQUIRED*
* @param string $geomfield The PostGIS geometry field *REQUIRED*
* @param string $srid The SRID of the returned GeoJSON *OPTIONAL (If omitted, 
 EPSG: 4326 will be used)*
* @param string $fields Fields to be returned *OPTIONAL (If omitted, all fields will 
 be returned)* NOTE- Uppercase field names should be wrapped in double quotes
* @param string $parameters SQL WHERE clause parameters *OPTIONAL*
* @param string $orderby SQL ORDER BY constraint *OPTIONAL*
* @param string $sort SQL ORDER BY sort order (ASC or DESC) *OPTIONAL*
* @param string $limit Limit number of results returned *OPTIONAL*
* @param string $offset Offset used in conjunction with limit *OPTIONAL*
* @return string resulting geojson string
*/

i hope it helps you...

Answer 2

ST_GeometryFromText( 'POINT($yourlon $yourlat)' )

Answer 3

Here's a very basic PHP solution which will convert from your input format to your output format:

$coord_array = sscanf($input, "lon = %f, lat = %f");
$x_coord = $coord_array[0];
$y_coord = $coord_array[1];
$output = "((" . $x_coord . " " . $y_coord . "))";

Sample data:

$input:
lon = 12.34, lat = 56.78
$output:
((12.34 56.78))

Answer 4

You writing an actionListener to be served with PHP.

First replace the call you using at your client from

map.getLonLatFromViewPort(ext.xy)

to

map.getLonLatFromViewPort(ext.xy).toShortString()

Parse the query parameter at server side using the explode function and check them with is_numeric.

You must also pass the map projection code or use a built in constant projection value. With coordinate and projection code you can create a new point geometry easily with postgis.

Replace the st_geometryfromtext with following:

$sql = "SELECT ST_SetSRID(ST_MakePoint( $lng, $lat ), $projCode );";

Adjust the query above using your favorite PHP pgsql access library. I recommend using the new standard PDO class.