[postgis-users] Postgis, OGR2OGR and QGIS

[pcreso at pcreso.com]

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

--- On Mon, 8/9/10, chrispg <chrisemberson at hotmail.com> wrote:

> From: chrispg <chrisemberson at hotmail.com>
> Subject: Re: [postgis-users] Postgis, OGR2OGR and QGIS
> To: postgis-users at postgis.refractions.net
> Date: Monday, August 9, 2010, 10:32 PM
> 
> 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
>