[gdal-dev] Ogr2ogr

Thu Nov 17 12:58:18 EST 2011

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 <fredjonze at gmail.com> 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 <
> chaitanya.ch at gmail.com> 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 <fredjonze at gmail.com>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 <
>>> sigfrido at tiscali.it> 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
>>> gdal-dev at lists.osgeo.org
>>> 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20111117/85dae0ee/attachment.html