Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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
1  
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: gaia-gis.it/spatialite-2.3.0/binaries.html I'll get my hands on the 3.0 stable version and see how things go. –  Bryan Goodrich Mar 20 '12 at 23:42
add comment

1 Answer 1

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
add comment

Your Answer

 
discard

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.