Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two tables: crime and (police) stations. The latter has only 4 records with (name, lat, lng) fields. The former has over 500K records with two California Zone 2 (ft) coordinate columns named X_Coord and Y_Coord, respectively. I use R to populate the SQLite database and am building a script to turn it spatial and prepare it for analysis.

My problem is that I can populate the crime geometry, but not the station geometry. I'm using the stand-alone spatialite executable. I run the init_spatialite.sql script. Then I run the four SQL commands below, with the last one failing: "SQL error: 'stations.geom' violates Geometry constraint [SRID not allowed]". I ran a boolean and count check to make sure the crime geometry matched the coordinates (they do). So for whatever reason, the same approach doesn't work on the stations table. I don't understand why I'm getting the constraint violation (the SRID exists in the metatable and is correct).

This may be neither here nor there, but the MakePoint function doesn't exist, even though it's in the documentation (and saw it as another possible approach to take).

SELECT AddGeometryColumn('crime',    'geom', 2226, 'POINT', 2);
SELECT AddGeometryColumn('stations', 'geom', 4326, 'POINT', 2);
UPDATE crime    SET geom = GeomFromText('POINT(' || X_Coord || ' ' || Y_Coord || ')', 2226);
UPDATE stations SET geom = GeomFromText('POINT(' || lng     || ' ' || lat     || ')', 4326);

EDIT: I followed the same routine in the spatialite-gui and it worked just fine. This is odd behavior, to say the least. I'll try this from home where I have (sudo) admin right, for today I've been practicing at work where I am a limited user.

share|improve this question
can you clarify what version and platform you are using? MakePoint works fine on my version. Have you checked that SRID 4326 exists in the spatial_ref_sys table? – Stev_k Mar 16 '12 at 1:35
Sorry for the delay, I completely forgot about this (I reference StackExchange, but rarely participate!). Checking the SRID in the metadata tables was the first thing I did, and it was there. Everything worked fine from Linux (for the most part). I noticed now that the download link I followed on Google sent me to the version 2.3 binaries: I'll get my hands on the 3.0 stable version and see how things go. – Bryan Goodrich Mar 20 '12 at 23:42
up vote 0 down vote accepted

Per my comment to Stev_k, I made sure to use the latest stable binaries for Windows, and in doing so I realized I messed up the static linking used in the CLI executable, which I believe is probably at the root of why some functionality was lost and some behavior was weird. I'm not quite sure the GUI was behaving differently, but that's another matter. Starting fresh was a good solution, and now my scripts work!

share|improve this answer

Your Answer


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.