I think the problem with ST_MakePoint is that it takes doubles and you're passing strings. You can try either adding a cast, or using ST_GeomFromText (but that takes WKT, not two points). So, if your coords are in the columns easting and northing, then you could try:
(using ST_MakePoint <http://postgis.refractions.net/docs/ST_MakePoint.html>and ST_SetSRID <http://postgis.refractions.net/docs/ST_SetSRID.html>) UPDATE Y14_header2 set the_geom = ST_SetSRID(ST_MakePoint(CAST(easting AS double precision), CAST(northing AS double precision)), 27700); (I think ST_SetSRID and SetSRID are the same except SetSRID is the old name) or (using ST_GeomFromText<http://postgis.refractions.net/docs/ST_GeomFromText.html> ) UPDATE Y14_header2 set the_geom = ST_GeomFromText('POINT (' || easting || ' ' || northing || ')',27700); Notice that in the second case I'm concatenating some text in there to make it WKT (WKT for a point is "POINT(x y)"). On Tue, Aug 10, 2010 at 4:53 AM, chrispg <[email protected]> wrote: > > 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. > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
