Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a map that is being hosted with the components of OpenGeo. The map contains WMS layers of millions of parcels and some rail stops. All of our spatial data is on PostgresSQL.

Right now the only interaction I have with Postgres is via OpenLayers/OpenGeo. I'd like to be able to send queries to the database via code (I guess JavaScript or PHP) so I can better interact with the data. For example, I'd like to be able to send a user entered parcel ID number to the database so I can get the latitude and longitude of the parcel, and then I then use OpenLayers to zoom to it. I'd also like to be able to get a list of the rail stop names and populate a drop down menu with them, then zoom to the rail stop when the user selects it in the drop down. I can write the queries I'd need in SQL and execute it in a console, but I am lost as to how to use PHP or JavaScript, etc to actually execute the query and return results. Does anyone have any suggestions or pointers as to where I can start to learn what I need to do.

Thank you very much for your help.

share|improve this question
    
you could just use WFS to pass the queries via CQL (or OGC) filters and save writing a new server. –  iant Jul 16 '13 at 5:25
    
I have been struggling a little bit with getting WFS to work so I was going to try to avoid that. I'll take another look though. Any idea how to just ping off of the Postgres server directly? Thanks. –  Jared Jul 18 '13 at 15:11

2 Answers 2

I'm sure if there is a more elegant way to do this , but you can use a php script to query the database, and modify the result of the query to your need (eg query the coordinate of a given parcel, and use those to change zoom and center with with yourMap.setCenter. My php script looks something like

<?php
// Connecting, selecting database
$dbconn = pg_connect("host=localhost dbname=test user= youruser password=yourpassword")
    or die('Could not connect: ' . pg_last_error());

// Performing SQL query
$query = 'SELECT departuretime FROM trajectories';
$result = pg_query($query) or die('Query failed: ' . pg_last_error());

// Fetch all the result in an array
$out=pg_fetch_all($result);
// encode the array to JSON format so it is usable in javascript
$myarr=json_encode($out);
echo $myarr;


// Free resultset
pg_free_result($result);

// Closing connection
pg_close($dbconn);

?>

This returns a JSON that can be used in javascript. From your JS script, you can call it through an AJAX request (I use Jquery):

var yourData;
$.ajax({
  type: 'GET',
  url: 'yourScript.php',
  dataType: 'json',
  success: function(data) {
    yourData=data;
    //and whatever you need 
  },
  data: {},
  async: false
});
console.log(yourData)

Don't forget to set async: false so the request is not done asynchronously (so you can modify yourdata or use it later in the script)

You may also want to check out one of the OpenGeo workshop (particularly this one), there is an example for doing an SQL query a postGIS database from a map click.

Again, there may (must?) be a more elegant solution, but for now, this has worked for me.

share|improve this answer

If you don't want to code alltogether, you can use QuantumGIS plugins to request data and view results. Cf. the blog of A. Graser for example.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.