[gdal-dev] Ogr2ogr

Fred Jones fredjonze at gmail.com
Thu Nov 17 15:53:39 EST 2011 

Whew...finally got it to work. Had to really unbend my mind to think
through all of the great advice you folks have given me. Here's the OGR
command that creates the point projection in mssql spatial:

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 geometry::STGeomFromText('POINT ('+str(xloc,20,20) + ' ' +
str(yloc,20,20) + ')',0) as ogr_geometry, xloc,yloc, spid  FROM
dbo.qaBtcSubLocs" -nln "btcSubLocs" -nlt POINT

It may still need some tweaking. I haven't layered it on other maps to see
if the points are in the proper locations. However, I think I'm a lot
closer.

Thanks to everyone who helped me!

I may have more questions. ;-)

Fred




On Thu, Nov 17, 2011 at 11:49 AM, Fred Jones <fredjonze at gmail.com> wrote:

> I think I was using the wrong spatial function. There is a STPointFromText
> function. However, when I try the following from SQL test (without OGR and
> using plug values for Montana State Plane NAD 27, and no SRID since I
> understand it should be zero) :
>
>
> select
> geography::STPointFromText('POINT(' + CAST(1020517.37616684 AS VARCHAR(20
> )) + ' ' +
>
> CAST(512438.56956808 AS VARCHAR(20)) + ')', 0)
> I get an error:
>
>
> A .NET Framework error occurred during execution of user-defined routine
> or aggregate "geography":
>
> System.FormatException: 24204: The spatial reference identifier (SRID) is
> not valid. The specified SRID must match one of the supported SRIDs
> displayed in the sys.spatial_reference_systems catalog view.
>
> System.FormatException:
>
>
>   On Thu, Nov 17, 2011 at 11:33 AM, Fred Jones <fredjonze at gmail.com>wrote:
>
>> There is a GEOGRAPHY::STGeomFromText('POINT(x,y)', srid) function in sql
>> server spatial. However, when I issue the 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" -sql
>> "SELECT geometry::STGeomFromText('POINT(xloc,yloc)',0) as ogr_geometry,
>> xloc,yloc, spid  FROM dbo.qaBtcSubLocs" -nln "btcSubLocs"
>>
>>
>>  I get the error:
>> ERROR 1: [Microsoft][ODBC SQL Server Driver][SQL Server]A .NET Framework
>> error occurred during execution of user-defined routine or aggregate
>> "geometry":
>> System.FormatException: 24141: A number is expected at position 10 of the
>> input.
>>  The input has xloc.
>> System.FormatException: at
>> Microsoft.SqlServer.Types.OpenGisWktReader.RecognizeDouble()
>>    at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePointText(Boolean
>> parseParentheses)
>>    at
>> Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType
>>
>> I'd like to keep this as a single command becuase this will be issued on
>> a server and I don't have local access to write files out under the sql
>> agent perms (long story about IT security restrictions).
>>
>> Any ideas?
>>
>> Fred
>>
>> On Thu, Nov 17, 2011 at 10:58 AM, Chaitanya kumar CH <
>> chaitanya.ch at gmail.com> wrote:
>>
>>> 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/b0f5b8d5/attachment-0001.html

More information about the gdal-dev mailing list