[postgis-users] Postgis, OGR2OGR and QGIS

[Jeff Adams]

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 <chrisemberson at hotmail.com> 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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100810/9e448a68/attachment.html>