[gdal-dev] Ogr2ogr
Fred Jones
fredjonze at gmail.com
Thu Nov 17 13:49:45 EST 2011
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/e00fa828/attachment.html
More information about the gdal-dev
mailing list