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 !!!

  • 2
    Firstly, you don't have to import your data to Postgres. You could, or you could use any number of other options, including SpatiaLite (gaia-gis.it/gaia-sins), which is a spatial enablement add-on for SQLite. The best solution will depend on whether you have any method of rendering in mind or a preferred choice of GIS software. Can you expand on how you intend to display your data? Jun 27, 2012 at 20:14
  • Hi thanks for the response. I planned to render the data using tilemill. And then to serve the layer. I really have some knowledge gaps about how to fit this all together so any advice would be appreciated. I will edit my original question to add more detail.
    – MB.
    Jun 27, 2012 at 21:41

2 Answers 2


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

  • Thanks I may try this method also for development purposes. Great answer.
    – MB.
    Jun 29, 2012 at 11:14

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

  • Thanks this is just the sort of specific answer that makes this sight great.
    – MB.
    Jun 29, 2012 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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