Fred, Now I see the problem. Your columns xloc and yloc are not really geometry columns. They are just a part of the point geometry. We need to make a geometry out of them.
If it was PostGIS, I would have just used the ST_GeomFromText() function to combine those two columns into a point geometry. I don't know if such a function exists in MSSQL. So I suggest you convert the whole database into another format like CSV, tweak it to make geometries from the points, and finally load it into a new table. The CSV driver page[1] shows how to convert the x and y values into point geometries. First run the following command. ogr2ogr -f CSV qaBtcSubLocs.csv "MSSQL:server=ELMER;database=HT_2011_10;trusted_connection=yes;tables=dbo.qaBtcSubLocs(xloc),dbo.qaBtcSubLocs(yloc)" -sql "SELECT xloc,yloc, spid FROM dbo.qaBtcSubLocs" Now create qaBtcSubLocs.vrt as described in the driver page but without the LayerSRS element. Finally run the following command. ogr2ogr -overwrite -s_srs EPSG:32002 -t_srs EPSG:4326 -f MSSQLSpatial "MSSQL:server=ELMER;database= CENSUS_2010;trusted_connection=yes" qaBtcSubLocs.vrt -nln "btcSubLocs" [1]: http://www.gdal.org/ogr/drv_csv.html On Thu, Nov 17, 2011 at 11:04 PM, Fred Jones <[email protected]> wrote: > Here is ogrinfo returned: > INFO: Open of > `MSSQL:server=ELMER;database=HT_2011_10;trusted_connection=yes' > using driver `MSSQLSpatial' successful. > > Here is my revised command: > ogr2ogr -overwrite -s_srs EPSG:32002 -t_srs EPSG:4326 -f MSSQLSpatial > "MSSQL:server=ELMER;database=CENSUS_2010;trusted_connection=yes" > "MSSQL:server=ELMER;database=HT_2011_10;trusted_connection=yes;tables=dbo.qaBtcSubLocs(xloc),dbo.qaBtcSubLocs(yloc)" > -sql "SELECT xloc,yloc, spid FROM dbo.qaBtcSubLocs" -nln "btcSubLocs" > > And here are the errors produced: > ERROR 1: Column type float is not supported for geometry column. > ERROR 1: Column type float is not supported for geometry column. > > Thx for your help. I've read every article I can google, but just haven't > found the right combination of flags to get this to work. > > Fred > > On Thu, Nov 17, 2011 at 9:35 AM, Chaitanya kumar CH < > [email protected]> wrote: > >> Fred, >> >> Check if your source table is being read correctly. >> Use ogrinfo. >> ogrinfo -al >> "MSSQL:server=ELMER;database=HT_2011_10;trusted_connection=yes" >> >> OGR doesn't read the MSSQL geometries if the source table doesn't have an >> entry in the geometry_columns table. You can bypass this by using the >> 'Tables' parameter in the connection string[1]. >> >> [1]: http://www.gdal.org/ogr/drv_mssqlspatial.html >> >> On Thu, Nov 17, 2011 at 9:28 PM, Fred Jones <[email protected]>wrote: >> >>> This is what I have so far. Back to where I started. The table is >>> created in CENSUS_2010 spatial database, but the ogr_geometry column is >>> null. No error. >>> >>> ogr2ogr -overwrite -s_srs EPSG:32002 -t_srs EPSG:4326 -f MSSQLSpatial >>> "MSSQL:server=ELMER;database=CENSUS_2010;;trusted_connection=yes" >>> "MSSQL:server=ELMER;database=HT_2011_10;trusted_connection=yes" -sql >>> "SELECT xloc,yloc, spid FROM dbo.qaBtcSubLocs" -nln "btcSubLocs" >>> The geometry_columns table is: >>> >>> >>> f_table_catalog f_table_schema f_table_name f_geometry_column >>> coord_dimension srid geometry_type >>> CENSUS_2010 dbo btcsublocs ogr_geometry 2 >>> 4326 GEOMETRY >>> >>> Fred >>> >>> On Thu, Nov 17, 2011 at 1:48 AM, Luca Sigfrido Percich < >>> [email protected]> wrote: >>> >>>> Hi Fred, >>>> >>>> Il giorno mer, 16/11/2011 alle 10.11 -0700, Fred Jones ha scritto: >>>> > Hi Sig, >>>> > >>>> > I have a SQL table temp_sublocs_btc with the MT State Plane x and y as >>>> > the first columns in the table. CENSUS_2010 is a sql spatial database. >>>> > >>>> > This is the command I have so far. When I execute it, I just get the >>>> > help returned, no error: >>>> > >>>> > ogr2ogr -overwrite -s_srs EPSG:32100 -t_srs EPSG:4326 -f >>>> > "MSSQL:server=ELMER;database=CENSUS_2010;trusted_connection=yes" >>>> > >>>> "MSSQL:server=ELMER;database=HT_2010_10;tables=temp_sublocs_btc;trusted_connection=yes" >>>> > >>>> > What am I doing wrong? >>>> >>>> Please remember to always post the error messages otherwise we won't be >>>> able to understand. Before the usege text you should see an error >>>> message. >>>> >>>> I never worked with ogr and MSSQL, but I guess that the problem is that >>>> you should separate the output format specification (-f "MSSQL") and >>>> the server connection string. >>>> >>>> Try: >>>> >>>> ogr2ogr -overwrite -s_srs EPSG:32100 -t_srs EPSG:4326 -f "MSSQL" >>>> "MSSQL:server=ELMER;database=CENSUS_2010;trusted_connection=yes" >>>> >>>> "MSSQL:server=ELMER;database=HT_2010_10;tables=temp_sublocs_btc;trusted_connection=yes" >>>> >>>> assuming that tables=temp_sublocs_btc allows you to select the input >>>> layer. >>>> >>>> Regarding the creation of point, in PostGIS I would simply insert the X >>>> and Y coords in two float fields of the target table, and issue a >>>> >>>> update CENSUS_2010.temp_sublocs_btc set geom = >>>> ST_Transform(ST_SetSRID(ST_MakePoint(x, y), 32100), 4326) >>>> >>>> Which means create a point with x, y, set its SRID to state plane, then >>>> transform it into WGS84, then store it in the geom column of your table >>>> (the geometry column of which should have been created in WGS84 SRS). >>>> >>>> I don't know the corresponding function in MSSQL for ST_MakePoint etc... >>>> you should refer to the documentation >>>> >>>> I never tried this! I don't know if you can do the transformation >>>> directly with the -sql clause of ogr2ogr or with other options, please >>>> try and let us know. >>>> >>>> Sig >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> _____________ >>>> PRIVACY >>>> Le informazioni contenute in questo messaggio sono riservate e >>>> confidenziali. Il loro utilizzo e' consentito esclusivamente al >>>> destinatario del messaggio, per le finalità indicate nel messaggio stesso. >>>> Qualora Lei non fosse la persona a cui il presente messaggio è destinato, >>>> La invitiamo ad eliminarlo dal Suo Sistema e a distruggere le varie copie o >>>> stampe, dandone gentilmente comunicazione all’indirizzo mail del mittente. >>>> Ogni utilizzo improprio e' contrario ai principi del D.lgs 196/03 e alla >>>> legislazione europea (Direttiva 2002/58/CE). >>>> >>>> PRIVACY >>>> Le informazioni contenute in questo messaggio sono riservate e >>>> confidenziali. Il loro utilizzo e' consentito esclusivamente al >>>> destinatario del messaggio, per le finalità indicate nel messaggio stesso. >>>> Qualora Lei non fosse la persona a cui il presente messaggio è destinato, >>>> La invitiamo ad eliminarlo dal Suo Sistema e a distruggere le varie copie o >>>> stampe, dandone gentilmente comunicazione all’indirizzo mail del mittente. >>>> Ogni utilizzo improprio e' contrario ai principi del D.lgs 196/03 e alla >>>> legislazione europea (Direttiva 2002/58/CE). >>>> >>> >>> >>> _______________________________________________ >>> gdal-dev mailing list >>> [email protected] >>> http://lists.osgeo.org/mailman/listinfo/gdal-dev >>> >> >> >> >> -- >> Best regards, >> Chaitanya kumar CH. >> >> +91-9494447584 >> 17.2416N 80.1426E >> > > -- Best regards, Chaitanya kumar CH. +91-9494447584 17.2416N 80.1426E
_______________________________________________ gdal-dev mailing list [email protected] http://lists.osgeo.org/mailman/listinfo/gdal-dev
