type Geometry from Integers

General talk
1

Hello,

in standard mapnik (or osm2psql) database with postgis extension is table planet_osm_nodes. There are columns lat and lon of integer type. I need in this table column way(of geomettry type) to be able to render all nodes (on really small area). I have created column

SELECT AddGeometryColumn (‘public’,‘planet_osm_nodes’,‘way’,900913,‘POINT’,2);

but i am not able to successfully convert integers to coordinates to call

UPDATE planet_osm_nodes
SET way=ST_GeomFromText(‘POINT(’||lon||’ ‘||lat||’)', 900913);

I have found some postgis function ST_Transform - it seems it converts from spatial reference system to another, but by default in database is only one spatial reference system with id 900913 and it is the one used in other tables (probalby WGS84). And i have really no idea, what spatial system (if any) are two integers.

Does anybody know how to transform lan and lot to Geometry?

Thanks a lot

2

I have found, that these integes could be spatial_ref_sys 4326.

I had no record in table spatial_ref_sys with id 4326, so I inserted

INSERT INTO spatial_ref_sys(
srid, auth_name, auth_srid, srtext, proj4text)
VALUES (4326,‘EPSG’,4326,‘GEOGCS[“WGS 84”,DATUM[“WGS_1984”,SPHEROID[“WGS
84”,6378137,298.257223563,AUTHORITY[“EPSG”,“7030”]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY[“EPSG”,“6326”]],PRIMEM[“Greenwich”,0,AUTHORITY[“EPSG”,“8901”]],UNIT[“degree”,0.01745329251994328,AUTHORITY[“EPSG”,“9122”]],AUTHORITY[“EPSG”,“4326”]]’,'+proj=longlat
+ellps=WGS84 +datum=WGS84 +no_defs ’
);

Now I am able to do

select
astext(ST_Transform(ST_SetSRID(ST_MakePoint(lon/100,lat/100),900913),4326)) from planet_osm_nodes;

and it returns

“POINT(14.4074320549884 50.0747194318404)”
“POINT(14.4126243173306 50.0787664877919)”
“POINT(14.4181399731751 50.0738603973842)”
“POINT(14.4240598708975 50.0722057037524)”
“POINT(14.4159570670347 50.0782995372314)”
…

BUT back on the trees…

UPDATE planet_osm_nodes
SET way=(ST_GeomFromText(‘POINT(’||lon/100||’ ‘||lat/100||’)', 900913))

/100 is enought

no spatial system conversion is needed…