2

I've a point layer in Spatialite in EPSG32632 system reference.

Then I've added two columns to calculate x, y coordinates in this way

ALTER TABLE points ADD COLUMN X REAL
ALTER TABLE points ADD COLUMN Y REAL

and calculate the coordinates in this way

SELECT load_extension('mod_spatialite');
UPDATE points SET X=ST_X("Geometry") , Y=ST_Y("Geometry")

Now I need to calculate the Lat, Lon values too.

I've tried this

ALTER TABLE points ADD COLUMN LON double;
ALTER TABLE points ADD COLUMN LAT double;
UPDATE points SET LON=ST_X(ST_Transform(geometry,4326));
UPDATE points SET LAT=ST_Y(ST_Transform(geometry,4326));

No errors in the execution but if I try

select X,Y,LON,LAT from points;

the result is

492051.358020695|4927339.50750624||
488162.133600385|4919734.42606608||
527761.520720489|4906898.57798871|| 

so, LON and LAT are empty.

Suggestions?

  • 1
    Hi Cesare, it's my fault (I've just updated my previous answer, sorry)... Consider LON and LAT as REAL, not double. These are the datatypes in SQLite 3: sqlite.org/datatype3.html – Antonio Falciano Apr 18 '17 at 19:14
  • Hi @afalciano, I've tried also with REAL but the fields are still empty ... :-( – Cesare Apr 18 '17 at 19:21
  • 1
    @afalciano Double is a valid name for a datatype in SQLite. Almost anything is because of the dynamic type system as you can read from the document that you cited. The Chapter 3.1.1. Affinity Name Examples even mentiones "double". – user30184 Apr 18 '17 at 20:14
  • @user30184 Ok for the double datatype, however I can reproduce the error in SpatiaLite GUI creating a db via ogr2ogr: ogr2ogr -f SQLite punti.sqlite points.shp. Maybe it happens for some SpatiaLite gotchas. – Antonio Falciano Apr 18 '17 at 20:32
  • 1
    What if you do ogr2ogr -f SQLite -dsco spatialite=yes -a_srs epsg:32632 punti.sqlite points.shp? With your command the result is a FDO style SQLite database, not a SpatiaLite db. -a_srs is not necessary if GDAL can find the source SRS. That can be tested with ogrinfo -al -so points.shp. If re-projection is needed then naturally -t_srs is needed, perhaps together with -s_srs. – user30184 Apr 18 '17 at 20:36
3

I don't see anything wrong in your SQL. I reproduced your case and as you can see I have lon and lat columns populated.

create table points (id integer, x double, y double, lon double, lat double);
select AddGeometryColumn('points','geometry',32632,'POINT');
insert into points (id,geometry) values (1,ST_GeomFromText('POINT (492051.358020695 4927339.50750624)',32632));
UPDATE points SET X=ST_X("Geometry") , Y=ST_Y("Geometry");
UPDATE points SET LON=ST_X(ST_Transform(geometry,4326));
UPDATE points SET LAT=ST_Y(ST_Transform(geometry,4326));

SELECT ROWID, "id", "x", "y", "lon", "lat", "geometry"
FROM "points";

1   1   492051.358021   4927339.507506  8.900015    44.499329   BLOB sz=60 GEOMETRY
  • I've tried your code and it works fine for me too. This means that something strange is in my data .. I'll check for this!! – Cesare Apr 18 '17 at 20:16
  • 2
    I would check first that your table "points" is really registered as EPSG:32632 in geometry_columns. If SpatiaLite does not know the source SRS it does not know how to convert coordinates into the target SRS. – user30184 Apr 18 '17 at 20:19
  • You're right!! I've checked and there was "0" as srid ... Now I've changed to "32632" and all works fine! – Cesare Apr 18 '17 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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