I have an sqlite 3 database with 20K entries each of which has lat long data and I wanted to create a layer from that data. From what I have read I need to import it into a postrgres DB but I'm not sure how to make sure that each item populates the database correctly (ie has all of the correct attributes completed).
my plan is to have a series of tiles created from the result of the query which would be a transparent background with the points overlayed and then allow clicking on them I still have a lot to learn to get this done but I want to ensure i put the data in the correct place to start with.
I am planning to do several things. 1. To render some GEOtiffs into tiles to act as base layer along with using OSM as a layer. I then intend to take my SQLite data and create an overlay layer or points that should be clickable. I wanted everything in a SERVER DBMS for several reasons.
- There could be up to 6,000 users accessing the data at any one time so I really wanted a robust DBMS UNDER THE HOOD.
- Also if I end up having to do some real time rendering on other data I can still store it in the same repository.
- I can exclude points based on certain criterion and create multiple layers from the same data.
My current understanding is that it would work as follows:
- Create a DB of points by importing SQLite data into POSTGRES with postGIS extensions (ensuring the correct GIS fields get populated appropriately)
- Use that DB and a query to access points and create a layer to render from within tilemill.
- Export that layer back into Postgres so that it can act as the layer server for my open layers based client app.
I have tilemill, qGIS, OPENGEO, software.
Please let me know if I have misunderstood how to do this !!!