[postgis-users] Postgis, OGR2OGR and QGIS

[Jeff Adams]

I have never used OGR2OGR so I can't offer much, but I can point out that:

-- so trying reprojection:
-- UPDATE Y14_header2 SET wkb_geometry = ST_SetSRID(wkb_geometry, 27700);
-- DOES NOT WORK

This is not reprojection.  ST_SetSRID just sets the SRID, it doesn't
reproject.  So POINT(1 1) in WGS84 will become POINT(1 1) in 27700, which
will most likely not be the same location.  To reproject you want
ST_Transform <http://postgis.refractions.net/docs/ST_Transform.html>.



On Mon, Aug 9, 2010 at 6:32 AM, chrispg <chrisemberson at hotmail.com> wrote:

>
> Can anyone help me with this query? its proving to be a real blocker and
> I've
> a feeling its something small I must be missing..
> Thanks for your help
>
>
> chrispg wrote:
> >
> > I am having trouble using OGR2OGR to load a .csv file into postgis and
> > using QGIS to view the result. Even though I am setting the SRID to 27700
> > for BNG I am unable to succeed in what sounds like a straightforward
> > task... Below are the steps I have taken. Any help is much appreciated.
> >
> > create a new database..
> > sudo -u postgres -i -H
> > createdb -E UTF8 -O testuser YMNC
> > createlang plpgsql YMNC
> > psql -d YMNC -f /usr/share/postgresql/8.3/contrib/_int.sql
> > psql -d YMNC -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql
> > psql YMNC -c "ALTER TABLE geometry_columns OWNER TO testuser"
> > psql YMNC -c "ALTER TABLE spatial_ref_sys OWNER TO testuser"
> > exit
> >
> > Then use ogr2ogr. Create settings file - YMNC.ovf...
> >
> > <OGRVRTDataSource>
> >         <OGRVRTLayer name="Y14_header2">
> >         <SrcDataSource>Y14_header2.csv</SrcDataSource>
> >         <SrcLayer>Y14_header2</SrcLayer>
> >         <GeometryField encoding="PointFromColumns" x="EASTING"
> > y="NORTHING"/>
> >         <GeometryType>wkbPoint</GeometryType>
> >         </OGRVRTLayer>
> > </OGRVRTDataSource>
> >
> > Use OGR to load csv file...
> >
> > ogr2ogr -f "PostgreSQL" -select "EASTING,NORTHING" PG:"host=127.0.0.1
> > user=testuser dbname=YMNC password=" -a_srs "EPSG:27700" YMNC.ovf
> >
> > The Y14_header2 table is visible in QGIS...
> > but CRS is WGS_84??!!
> >
> > so trying reprojection:
> > UPDATE Y14_header2 SET wkb_geometry = ST_SetSRID(wkb_geometry, 27700);
> > DOES NOT WORK
> >
> > so try dropping the column and creating new geometry column:
> > SELECT DropGeometryColumn ('public', 'Y14_header2', 'wkb_geometry');
> > THIS WORKS
> >
> > Add a new geometry column, and call it the_geom instead:
> > SELECT AddGeometryColumn ('public', 'Y14_header2', 'the_geom', 27700,
> > 'POINT',2);
> > THIS WORKS
> >
> > but data no longer visible in QGIS..........
> >
> > so try re-setting the SRID....
> > UPDATE Y14_header2 SET the_geom = ST_SetSRID(the_geom, 27700);
> > THIS WORKS
> >
> > but data still not visible in QGIS..........
> >
>
> --
> View this message in context:
> http://old.nabble.com/Postgis%2C-OGR2OGR-and-QGIS-tp29340858p29386505.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/20100809/0f650180/attachment.html>