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 <fredjo...@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 <fredjo...@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...@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 <fredjo...@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...@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 <fredjo...@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 <
>>>>>> sigfr...@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@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
>>>
>>
>>
>
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/gdal-dev
