1

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.

2
  • 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, 2012 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. Mar 20, 2012 at 23:42

1 Answer 1

0

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!

Your Answer

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

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