[postgis-users] Postgis, OGR2OGR and QGIS

[chrispg]

Thanks Jeff and Brent for your replies.
The re-project vs SetSRID is a useful tip, but all along I thought the data
was in the wrong place - given Brent's reply and the fact I cannot see any
points in QGIS probably means that I need to populate the table, so I ran
this...

UPDATE Y14_header2 set the_geom = SetSRID(ST_MakePoint(easting, northing),
27700);

and received this error....

ERROR:  function st_makepoint(character varying, character varying) does not
exist
LINE 1: UPDATE Y14_header2 set the_geom = setsrid(ST_makepoint(easting,...
                                                  ^
HINT:  No function matches the given name and argument types. You might need
to add explicit type casts.

I also tried ST_GeomFromText but still the same error.

Any ideas?
TIA


Hi Chris,

The steps you carried out were fine, but you need to repopulate your
geometry column.

dropgeometrycolumn() removes the column from the table
creategeometrycolumn() creates a new EMPTY column
   (does not create any values in that column)
the first update setsrid() tries to set geometries to a different  
   SRID than that specified for the geometry column, so fails
the second update setsrid() modifies all the non-existent 
   geometry value (does nothing because there are no values to 
   update, but is a semantically correct statement, so does not 
   fail) 

There is still nothing in that column for QGIS to plot

I don't know why ogr2ogr created a WGS84 geometry column instead of a
EPSG:27700 one, but it is easy to fix...

Assuming you now have 2 columns in your table, northing & easting, which are
the coords in EPSG:27700 as numeric values:

-- your sql statement to create the column is fine
SELECT AddGeometryColumn ('public', 'Y14_header2', 'the_geom', 
27700,'POINT',2);

-- but you need to populate it
UPDATE Y14_header2 set the_geom = setsrid(ST_makepoint(easting, northing),
27700);


This will generate point values from the northing & easting columns in the
table, assign the appropriate SRID & write them to the table.

If you did want WGS84 versions of these you could run:

SELECT AddGeometryColumn ('public', 'Y14_header2',> 'wgs84_geom', 
4326,'POINT',2);
UPDATE Y14_header2 set wgs84_geom = ST_transform(the_geom, 4326);

which creates a second geometry column in the table in the new projection, &
sets the values in this column to points in the wgs84 coord srid


HTH,

  Brent Wood
-- 
View this message in context: http://old.nabble.com/Postgis%2C-OGR2OGR-and-QGIS-tp29340858p29395586.html
Sent from the PostGIS - User mailing list archive at Nabble.com.