Incremental retrieval of data from a database - OpenLayers

Question

I have a new task now:

The user will input the lat-lon values in a HTML form and when an INSERT button is clicked these values should be stored the database. Now when a submit button is clicked the markers of all the lat-lon values stored in a table in the database should be visible on the map. This is fairly not a tough task, but... when the user provides next value of a point, this should be stored in the same table and this particular point marker should be added to the map (the map already contains markers of previous points)... that is, instead of reading the whole data (including the new point) from the database and projecting on the map, since before adding new point to the database we have already pointed the markers of previous values, now only the new point marker should be added to the map.

For better understanding here is the example...

I already have 999 points in my database and these are pointed on the map. Now the user gives 1000th point values and this will be stored in the DB. now if we reload (click on submit button) the page, instead of reading all the 1000 points from the database and create markers, it should only read the new point and add this to already existing map with 999 markers.

I have a simple logic for this, table1 in the database contains all the values and table2 only the new value. Whenever the user insert a points it is simultaneously added in both the tables and when the user submits the only value from table2 is deleted (so that next new value can be stored). So reading just from table2 works here, but how to add this point to the already existing map is the main task.

I don't have any idea whether this can be done in OpenLayers or not, so please help me how to do this.

Hope my intention is understandable.

Till now I have not worked with databases in OpenLayers, so please suggest me which database (like MySQL, etc...) suits better for this task... and any other suggestions on storing/retrieving to/from the database.

Answer 1

First of all, of course you will ONLY use one table.

Then, I would give you the advice to store each lat/long couples with an associated ID or/and a timestamp. When you first query the database to display stored coordinates, just store (in a js var) the maximum value of returned IDs or the newest timestamp.

When you have to query again the DB, use your timestamp or your max ID value to query only the needed records with a WHERE clause.

You would have something similar to :

SELECT * FROM mytable where created_date > 1323334533;

I don't know if you can add data to an existing layer with openlayers, but for sure you can create a new one to display new records, since it appears to make sense to separate them.

Answer 2

You could use WFS to fetch the data from a server (abstract the database out of the picture) and then use WFS-T to send updates back to the server while just adding the point to the vector layer directly in OpenLayers.

Answer 3

If I read your description accurately, the cause of the problems you are having appears to be buried in this small phrase:

if we reload(click on submit button) the page

Web pages are mostly stateless: The amount of information carried across two independent views of pages (even the same one) is very limited, and the mechanisms for doing so are clunky. (Cookies, mostly).

What happens when you click "submit" and the page begins to reload is that, essentially, all the work done to get to that point is thrown away and the web browser begins again from a blank slate. The work thrown away includes all of the data OpenLayers had fetched before button was clicked.

Under those circumstances, one way or another, the server will likely have to resend the full set of results (including the addition) back to the browser on reloading page. The problem doesn't have very much to do with OpenLayers or the database you're using; instead it comes from the way HTML pages work.

The solution: Don't reload (or otherwise leave) the page! Instead of having the submit button perform a full form submission, which navigates away from the current page, instead connect it to Javascript which:

• Submits the new point data to the server using AJAX

• On getting a success back from the server, add the new point to the layer using OpenLayer's API.

Hopefully this is a good starting point -- there's a huge number of resources on AJAX & Javascript out there, in case you need more tutorial-style content. Good luck!

Answer 4

I don't know the details in how to handle it on the OL side.

Your approach with 2 tables I think sounds dangerous. If there is more than one user using the application you will always risk that things gets messed up.

As I said I don't know the OL part but maybe you can add your point directly without sending it to the server if you can build a geometry representation in javascript that OL can handle.

Otherwise if you choose PosgreSQL/PostGIS as your database (I might be biased, but it should be a quite simple choise ;-) ) then you have the possibility to get a return from your insert query.

So the database can return the geometry in the geometry format of your choice as a response to your insert query. http://www.postgresql.org/docs/9.0/interactive/sql-insert.html

something like:

INSERT INTO the_table (the_geom) VALUES('POINT(41 15)'::geometry)
RETURNING st_astext(the_geom);

But in the example you will just get back the same WKT representation that you inserted.