<div>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) :</div>
<div> </div><font color="#0000ff" size="2"><font color="#0000ff" size="2">
<p>select</p></font></font><font size="2"> </font><font color="#0000ff" size="2"><font color="#0000ff" size="2">geography</font></font><font color="#808080" size="2"><font color="#808080" size="2">::</font></font><font size="2">STPointFromText</font><font color="#808080" size="2"><font color="#808080" size="2">(</font></font><font color="#ff0000" size="2"><font color="#ff0000" size="2">'POINT('</font></font><font size="2"> </font><font color="#808080" size="2"><font color="#808080" size="2">+</font></font><font size="2"> </font><font color="#ff00ff" size="2"><font color="#ff00ff" size="2">CAST</font></font><font color="#808080" size="2"><font color="#808080" size="2">(</font></font><font size="2">1020517.37616684 </font><font color="#0000ff" size="2"><font color="#0000ff" size="2">AS</font></font><font size="2"> </font><font color="#0000ff" size="2"><font color="#0000ff" size="2">VARCHAR</font></font><font color="#808080" size="2"><font color="#808080" size="2">(</font></font><font size="2">20</font><font color="#808080" size="2"><font color="#808080" size="2">))</font></font><font size="2"> </font><font color="#808080" size="2"><font color="#808080" size="2">+</font></font><font size="2"> </font><font color="#ff0000" size="2"><font color="#ff0000" size="2">' '</font></font><font size="2"> </font><font color="#808080" size="2"><font color="#808080" size="2">+</font></font><font size="2">
<p></p></font><font color="#ff00ff" size="2"><font color="#ff00ff" size="2">CAST</font></font><font color="#808080" size="2"><font color="#808080" size="2">(</font></font><font size="2">512438.56956808 </font><font color="#0000ff" size="2"><font color="#0000ff" size="2">AS</font></font><font size="2"> </font><font color="#0000ff" size="2"><font color="#0000ff" size="2">VARCHAR</font></font><font color="#808080" size="2"><font color="#808080" size="2">(</font></font><font size="2">20</font><font color="#808080" size="2"><font color="#808080" size="2">))</font></font><font size="2"> </font><font color="#808080" size="2"><font color="#808080" size="2">+</font></font><font size="2"> </font><font color="#ff0000" size="2"><font color="#ff0000" size="2">')'</font></font><font color="#808080" size="2"><font color="#808080" size="2">,</font></font><font size="2"> 0</font><font color="#808080" size="2"><font color="#808080" size="2">)
<div></div></font></font>I get an error:
<div> </div>
<div><font size="1">
<p>A .NET Framework error occurred during execution of user-defined routine or aggregate "geography": </p>
<p>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.</p>
<p>System.FormatException: </p></font><br><br></div>
<div class="gmail_quote">On Thu, Nov 17, 2011 at 11:33 AM, Fred Jones <span dir="ltr"><<a href="mailto:fredjonze@gmail.com">fredjonze@gmail.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex" class="gmail_quote">
<div>There is a <span style="COLOR: blue">GEOGRAPHY</span><span style="COLOR: gray">::</span>STGeomFromText<span style="COLOR: gray">(</span><span style="COLOR: red">'POINT(x,y)'</span><span style="COLOR: gray">, </span>srid<span style="COLOR: gray">) <font color="#000000">function in sql server spatial. However, when I issue the command:</font></span></div>
<div><span style="COLOR: gray"><font color="#000000"></font></span> </div>
<div><span style="COLOR: gray">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"</span></div>
<div><span style="COLOR: gray"></span> </div>
<div><span style="COLOR: gray"></span><br> I get the error:</div>
<div>ERROR 1: [Microsoft][ODBC SQL Server Driver][SQL Server]A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":<br>System.FormatException: 24141: A number is expected at position 10 of the input.<br>
The input has xloc.<br>System.FormatException: at Microsoft.SqlServer.Types.OpenGisWktReader.RecognizeDouble()<br> at Microsoft.SqlServer.Types.OpenGisWktReader.ParsePointText(Boolean parseParentheses)<br> at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType</div>
<div> </div>
<div>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).</div>
<div> </div>
<div>Any ideas?</div>
<div> </div><font color="#888888">
<div>Fred</div></font>
<div>
<div></div>
<div class="h5">
<div> </div>
<div class="gmail_quote">On Thu, Nov 17, 2011 at 10:58 AM, Chaitanya kumar CH <span dir="ltr"><<a href="mailto:chaitanya.ch@gmail.com" target="_blank">chaitanya.ch@gmail.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex" class="gmail_quote">Fred,<br><br>Now I see the problem.<br>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.<br>
<br>If it was PostGIS, I would have just used the ST_GeomFromText() function to combine those two columns into a point geometry.<br><br>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.<br>
<br>First run the following command.<br><span style="FONT-FAMILY: courier new, monospace">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"</span><br style="FONT-FAMILY: courier new, monospace">
<br>Now create qaBtcSubLocs.vrt as described in the driver page but without the LayerSRS element.<br>Finally run the following command.
<div><br><span style="FONT-FAMILY: courier new, monospace">ogr2ogr -overwrite -s_srs EPSG:32002 -t_srs EPSG:4326 -f MSSQLSpatial "MSSQL:server=ELMER;database=</span></div>
<div style="FONT-FAMILY: courier new, monospace">CENSUS_2010;trusted_connection=yes" qaBtcSubLocs.vrt -nln "btcSubLocs"</div><br>[1]: <a href="http://www.gdal.org/ogr/drv_csv.html" target="_blank">http://www.gdal.org/ogr/drv_csv.html</a>
<div>
<div></div>
<div><br><br>
<div class="gmail_quote">On Thu, Nov 17, 2011 at 11:04 PM, Fred Jones <span dir="ltr"><<a href="mailto:fredjonze@gmail.com" target="_blank">fredjonze@gmail.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0pt 0pt 0pt 0.8ex; PADDING-LEFT: 1ex" class="gmail_quote">
<div>Here is ogrinfo returned:</div>
<div>INFO: Open of `MSSQL:server=ELMER;database=HT_2011_10;trusted_connection=yes'<br> using driver `MSSQLSpatial' successful.</div>
<div> </div>
<div>Here is my revised command:</div>
<div>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" </div>
<div> </div>
<div>And here are the errors produced:</div>
<div>ERROR 1: Column type float is not supported for geometry column.<br>ERROR 1: Column type float is not supported for geometry column.<br><br>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.</div>
<div> </div><font color="#888888">
<div>Fred</div></font>
<div>
<div></div>
<div>
<div> </div>
<div class="gmail_quote">On Thu, Nov 17, 2011 at 9:35 AM, Chaitanya kumar CH <span dir="ltr"><<a href="mailto:chaitanya.ch@gmail.com" target="_blank">chaitanya.ch@gmail.com</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex" class="gmail_quote">Fred,<br><br>Check if your source table is being read correctly.<br>Use ogrinfo.<br><span style="FONT-FAMILY: courier new, monospace">ogrinfo -al "MSSQL:server=ELMER;database=HT_2011_10;trusted_connection=yes"</span><br>
<br>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].<br><br>[1]: <a href="http://www.gdal.org/ogr/drv_mssqlspatial.html" target="_blank">http://www.gdal.org/ogr/drv_mssqlspatial.html</a><br>
<br>
<div class="gmail_quote">
<div>
<div></div>
<div>On Thu, Nov 17, 2011 at 9:28 PM, Fred Jones <span dir="ltr"><<a href="mailto:fredjonze@gmail.com" target="_blank">fredjonze@gmail.com</a>></span> wrote:<br></div></div>
<blockquote style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0pt 0pt 0pt 0.8ex; PADDING-LEFT: 1ex" class="gmail_quote">
<div>
<div></div>
<div>
<div>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.</div>
<div> </div>
<div>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" <br>
</div>
<div>The geometry_columns table is:</div>
<div> </div>
<div>f_table_catalog f_table_schema f_table_name f_geometry_column coord_dimension srid geometry_type<br>CENSUS_2010 dbo btcsublocs ogr_geometry 2 4326 GEOMETRY</div>
<div><br><font color="#888888">Fred</font></div>
<div>
<div></div>
<div>
<div> </div>
<div class="gmail_quote">On Thu, Nov 17, 2011 at 1:48 AM, Luca Sigfrido Percich <span dir="ltr"><<a href="mailto:sigfrido@tiscali.it" target="_blank">sigfrido@tiscali.it</a>></span> wrote:<br>
<blockquote style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex" class="gmail_quote">Hi Fred,<br><br>Il giorno mer, 16/11/2011 alle 10.11 -0700, Fred Jones ha scritto:<br>
<div>> Hi Sig,<br>><br>> I have a SQL table temp_sublocs_btc with the MT State Plane x and y as<br>> the first columns in the table. CENSUS_2010 is a sql spatial database.<br>><br>> This is the command I have so far. When I execute it, I just get the<br>
> help returned, no error:<br>><br>> ogr2ogr -overwrite -s_srs EPSG:32100 -t_srs EPSG:4326 -f<br>> "MSSQL:server=ELMER;database=CENSUS_2010;trusted_connection=yes"<br>> "MSSQL:server=ELMER;database=HT_2010_10;tables=temp_sublocs_btc;trusted_connection=yes"<br>
><br>> What am I doing wrong?<br><br></div>Please remember to always post the error messages otherwise we won't be<br>able to understand. Before the usege text you should see an error<br>message.<br><br>I never worked with ogr and MSSQL, but I guess that the problem is that<br>
you should separate the output format specification (-f "MSSQL") and<br>the server connection string.<br><br>Try:<br>
<div><br>ogr2ogr -overwrite -s_srs EPSG:32100 -t_srs EPSG:4326 -f "MSSQL"<br>"MSSQL:server=ELMER;database=CENSUS_2010;trusted_connection=yes"<br>"MSSQL:server=ELMER;database=HT_2010_10;tables=temp_sublocs_btc;trusted_connection=yes"<br>
<br></div>assuming that tables=temp_sublocs_btc allows you to select the input<br>layer.<br><br>Regarding the creation of point, in PostGIS I would simply insert the X<br>and Y coords in two float fields of the target table, and issue a<br>
<br>update CENSUS_2010.temp_sublocs_btc set geom =<br>ST_Transform(ST_SetSRID(ST_MakePoint(x, y), 32100), 4326)<br><br>Which means create a point with x, y, set its SRID to state plane, then<br>transform it into WGS84, then store it in the geom column of your table<br>
(the geometry column of which should have been created in WGS84 SRS).<br><br>I don't know the corresponding function in MSSQL for ST_MakePoint etc...<br>you should refer to the documentation<br><br>I never tried this! I don't know if you can do the transformation<br>
directly with the -sql clause of ogr2ogr or with other options, please<br>try and let us know.<br><br>Sig<br><br><br><br><br><br><br><br><br>_____________<br>PRIVACY<br>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).<br>
<br>PRIVACY<br>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).<br>
</blockquote></div><br></div></div><br></div></div>
<div>_______________________________________________<br>gdal-dev mailing list<br><a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br><a href="http://lists.osgeo.org/mailman/listinfo/gdal-dev" target="_blank">http://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
</div></blockquote></div><font color="#888888"><br><br clear="all"><br>-- <br>Best regards,<br>Chaitanya kumar CH.<br><br><a href="tel:%2B91-9494447584" target="_blank" value="+919494447584">+91-9494447584</a><br>17.2416N 80.1426E<br>
</font></blockquote></div><br></div></div></blockquote></div><br><br clear="all"><br>-- <br>Best regards,<br>Chaitanya kumar CH.<br><br><a href="tel:%2B91-9494447584" target="_blank" value="+919494447584">+91-9494447584</a><br>
17.2416N 80.1426E<br></div></div></blockquote></div><br></div></div></blockquote></div><br>