How to put 20K points on a map

Question

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.

---

Further details.

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.

1. 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.
2. Also if I end up having to do some real time rendering on other data I can still store it in the same repository.
3. 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:

1. Create a DB of points by importing SQLite data into POSTGRES with postGIS extensions (ensuring the correct GIS fields get populated appropriately)
2. Use that DB and a query to access points and create a layer to render from within tilemill.
3. 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 !!!

Answer 1

Tlemill will accept data from a number of sources too, including SQLite. PostGIS is always a good choice for GIS, but I wouldn't necessarily give up on SQLite because, set up right, it can be faster than PostGIS, it is very lightweight and easier to use.

TileMill can use a regular SQLite db or the spatially enabled version, however whether you want your data in PostGIS or SQLite/SpatiaLite, the geometry needs to be in an OGR compatible WKB format.

The easiest way to get a point file with simple lat/lon coordinate pairs into an OGR-compliant format would be export your table as a CSV and then use import it into QGis (blue button with three commas on it in top toolbar) and then right click on the layer and choose 'Save As'. In the Format drop-down box, select Spatialite and that's it.

To get the data into PostGis is a little more involved and there is a tutorial here: http://www.spatialdbadvisor.com/postgis_tips_tricks/118/loading-point-data-from-a-csv-file-in-postgis

Answer 2

I never used SQLite so I can't really comment, but I would have thought that for a db that can handle 6000 users Postgres/PostGIS would be a better choice.

For importing the initial lat/lon data into Postgres, you can use the csv path as @Sulvester Sneeky suggested or use a SQLite dump as mentioned in this stackoverflow post.

Assuming then that you have the your points table in Postgres you can use the following statements to 'spatialize' it.

1. Create a new table which will hold the data and its attributes (you could use the same table but I always like to keep the original intact). Assuming your original table is called 'mypoints' with a pk field of point_id and the new table is called 'mypointsspatial' run:

create table mypointsspatial as select * from mypoints

This will just create a copy of the table. Then add a unique key to it:

CREATE UNIQUE INDEX point_id_pk ON mypointsspatial (point_id);

Now, add the spatial column:

SELECT AddGeometryColumn('mypointsspatial', 'point_geom', 4326, 'POINT', 2 )

where 4326 is the SRID (assuming your coordinated are in lat/lon)

Then create the points:

update mypointsspatial set point_geom=makepoint(lon,lat)

Finally, add a spatial index:

CREATE INDEX point_geom_spidx ON mypointsspatial USING GIST (point_geom );

You should now be able to view this layer in QGIS and then use tilemill to symbolize it