<div>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:</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 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</div>
<div> </div>
<div>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.</div>
<div> </div>
<div>Thanks to everyone who helped me!</div>
<div> </div>
<div>I may have more questions. ;-)</div>
<div> </div>
<div>Fred</div>
<div> </div>
<div><br><br> </div>
<div class="gmail_quote">On Thu, Nov 17, 2011 at 11:49 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>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>
<div></div>
<div class="h5">
<div class="gmail_quote">On Thu, Nov 17, 2011 at 11:33 AM, Fred Jones <span dir="ltr"><<a href="mailto:fredjonze@gmail.com" target="_blank">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>
<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></div></div></blockquote></div><br>