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.
