[GIS] PostGIS and geometry column

geometrypostgis-2.0

I have a csv table. Each record includes a start node (lat1,lon1) and an end node (lat2,lon2).
I have already imported the .csv into Postgresql 8.4. Now I would like set the geometry column. I tried to use this sql statement:

UPDATE mytable
SET the_geom = ST_GeomFromText('POINT(' || lat1 || ' ' || lon1 || ' ' ||
               lat2 || ' ' || lon2 || ')',4326);

but I receive this warning message:

WARNING:  OGC WKT expected, EWKT provided - use GeomFromEWKT() for this
            ERROR:  new row for relation "mytable" violates 
            check constraint "enforce_dims_the_geom"

Do you have any idea on how to add geometries in my case?

Thank you
Selini

Best Answer

You're constructing a POINT with four ordinates. To the parser that looks like a POINT ZM. You want to construct a LINESTRING.

ST_SetSRID(ST_MakeLine(ST_MakePoint(lon1, lat1), ST_MakePoint(lon2, lat2)), 4326)

Related Solutions

PostGIS – How to Populate the Geometry Column in a PostGIS Table?

Per comments, in occasions the geom column might not be visible to the client as a human readable string (a geom column is encoded as an EWTB - Extended Well Known Binary ).

There are two ways to visually check if the geom column is populated:

Load the table to your favourite gis client (eg. Qgis)
Count the NULL and NOT NULL values of the geom column :

      SELECT COUNT(geom), 'null_tally' AS narrative 
      FROM 'myTable' 
      WHERE a IS NULL 
      UNION
      SELECT COUNT(geom), 'not_null_tally' AS narrative 
      FROM 'myTable'  
      WHERE a IS NOT NULL;

count sql snippet from : https://stackoverflow.com/questions/1271810/counting-null-and-non-null-values-in-a-single-query )

PostGIS Geometry Column Precision – How to Determine with SELECT DISTINCT

I'm surprised it's quite so coarse, but there it is. It's not DISTINCT, per se, it's the '=' operator, which is defined for geometry as 'equality of the index keys' which means practically 'equality of the 32-bit bounding boxes'.

You can see the same effect just using '=' directly,

select 'POINT (0.000000001 0.000000001)'::geometry = 'POINT (0.000000001 0.000001)'::geometry;

select 'POINT (0.000000001 0.000000001)'::geometry = 'POINT (0.000000001 0.00001)'::geometry;

Making '=' behave "intuitively" would unfortunately involve either a huge computational loss (doing explicit ST_Equals() evaluation for the operator call) or some substantial new complicated code (storing hash values for larger geometries, doing exact tests on the fly for smaller ones, choosing the right code path on the fly, etc)

And of course now lots of applications / users have internalized the existing behaviour, such as it is, so "improving" it would be a downgrade for many folks. You can do an "exact" distinct by calculating your set on ST_AsBinary(geom) instead, which will do exact equality testing on the bytea outputs.

Best Answer

Related Solutions

PostGIS – How to Populate the Geometry Column in a PostGIS Table?

PostGIS Geometry Column Precision – How to Determine with SELECT DISTINCT

Related Question