Questions tagged [sql-server-spatial]
Microsoft SQL spatial database
129
questions
0
votes
0answers
17 views
Generate Points from inside SQL Geography
I have a shapefile which has been loaded into MS SQL-Server. Mostly polygons, but some multi-polygons as well.
For each record, I need to generate a list of say 50 points that are within the polygon. ...
0
votes
0answers
27 views
How to change projection when querying SQL Server
I am querying an SQL Server and would like to change the projection from 25832 (a commonly used European format) to EPSG = 4326, which is required by packages like Leaflet. I am aware that there are ...
0
votes
0answers
123 views
Warning 6 while loading data to MSSQLSpatial using ogr2ogr
I am loading layers from a .gdb to a database in SQL Server using the following command:
ogr2ogr -f MSSQLSpatial "MSSQL:server=server_name;database=database_name;trusted_connection=yes" "...
0
votes
1answer
27 views
Unable to connect to SQL Server tables with 10.5 ArcMap but it works with 10.7
I have a requirement where I need to connect to the SQL Server which I have created using ArcCatalog 10.7.
I have a requirement where I need to now use 10.5 version. But when I attempt to connect to ...
1
vote
1answer
34 views
SQL Server Spatial Enable Spatial column and Analysis
I have:
a table called ABC with columns (ID, County_Name, Lat, Long) in SQL
server pre-filled with the values under its respective columns,
except for County_Name column which is currently null.
an ...
2
votes
1answer
129 views
Can't convert a WKT with a Z value into a SQL Geometry value
I'm using nettopologysuite in c# to build a nettopology geometry object, and then save it as a geometry object in SQL. Generally this works fine, except i recent came up with (from a shapefile) the ...
0
votes
0answers
59 views
Export geodataframe with shapely polygon to sql server table (geometry)
I'm pulling open street map data into a geodataframe and looking to write the results to a sql server database. The geometry types are polygon and they are in shapely format. When I attempt to write ...
3
votes
2answers
221 views
Combining ST Buffer and Intersect with Where clause
I'm trying to create a buffer around a single point in a spatial entity and finding all the points from a different entity that fall within that buffer.
I tried two approaches. The code below runs ...
2
votes
2answers
65 views
Running ogr2ogr in Python on Windows
I run ogr2ogr to transfer geometrical data from a pgsql (PostGIS) database to a MS SQL one. It works quite well. I'm trying to achieve exactly the same result in Python, as I'm planning to use ...
0
votes
1answer
31 views
Loaded vector layers from SQL Server 2014 but data is not visible on the map in QGiS 3.8.3 - Zanzibar
This is odd - I've successfully connected to my spatial sql server 2014 instance, and loaded two point layers and one Polygon layer into QGIS. I see nothing on the map. I zoom to the layer, I know ...
1
vote
1answer
65 views
ogr2ogr MSSQLSpatial truncating attribute fields
I have a Shapefile with a number of attribute fields defined in its corresponding .dbf file. I am trying to transfer this data to an Azure SQL Server DB using the following command:
ogr2ogr -f ...
0
votes
0answers
43 views
Possible to import current version (v2) of the SQL Server Spatial binary format with QGIS/GDAL?
I'm running QGIS 3.4.3 on a Windows 10 machine and when loading vector layers from our MS SQL Server version 12 everything seems to be working great. No problems connecting or visualizing the data. ...
1
vote
0answers
58 views
Similarity between two sets of lat/long points
I am trying to measure the similarity between two trajectories (two sets of lat/long points) which are the outputs from two APIs. I am trying to measure the difference between the routes given by ...
1
vote
1answer
55 views
Polygon node order will not correct [duplicate]
I've got a few polygons (they were previously one multipolygon) which seem to insist on having an anticlockwise node order. As such, when running through various operations in SQL, they throw out the ...
0
votes
1answer
73 views
QGIS with MSSQLSpatial : no geometry. OgrInfo Corrupt data
I am having this issue while I am trying to connect to my MSSQLSpatial database.
So, I've created this features with just one record with geometry. I can see this feature on Microsoft Management ...
1
vote
0answers
41 views
QGIS 3.45 Layers Panel point data labels set too far apart
The Layers Panel in v 2.18 is fine but we are now trying to go live with 3.45 and are experiencing issues with the display of some layers in the panel.
Layers that are generated from an MSSQL spatial ...
1
vote
1answer
94 views
Extract intersecting polygon from multipolygon search
I'm using the latest SQL Server and trying to build a query where i can search a database of spatial data and find the intersecting polygon.
The problem is that the spatial data can be of ...
1
vote
1answer
101 views
ogr2ogr with GDAL 2.4 not reprojecting correctly
I have downloaded the latest OSGeo4w (GDAL 2.4) and GDAL 2.4 (from GIS Internals site) but I cant get the below command to correctly reproject the shapes in either application.
I used the command ...
1
vote
0answers
265 views
Connect to MSSQL from QGIS on Mac
I tried to connect to MSSQL from QGIS and I couldn't find any solution to my problem.
I install MSSQL on my Mac with Docker and try to connect QGIS with localhost(host=localhost) and left the DSN ...
3
votes
1answer
132 views
Can QGIS use MS SQL Server 2016
Does anyone know if there are any issues for QGIS in accessing MS SQL Server 2016?
I am currently using 2008, but there are a number of new functions within 2016 that are attractive for the upgrade.
0
votes
0answers
44 views
Connect Mapnik to MSSQL
I want to connect Mapnik to MSSQL Spatial SDE, but I don't know how to do this.
import mapnik
m = mapnik.Map(256,256)
mapnik.load_map(m, "test.xml")
m.zoom_all()
mapnik.render_to_file(m, "2.png")
...
1
vote
0answers
56 views
How to deal with decimal separator when uploading geometries to MSSQL server using shape2sql.exe?
I'm using Shape2Sql.exe to upload .shp shapefile to MSSQL server 13.0.4502.0 on a Win10 64bit laptop. I've added Shape2Sql.exe.config
<?xml version="1.0" encoding="utf-8"?>
<configuration>...
0
votes
0answers
230 views
MS SQL Server STEnvelope() performance
Using MS SQL Server 2012, when we write a query with
select GEOM.STEnvelope() from table
where table holds approx. 4000 native SQL Server line geometries of type geometry, with 2/6/180 points min/...
5
votes
1answer
421 views
Using new Australian Datum GDA2020 in SQL Server 2016?
We have several hundred datasets/spatial layers within SQL Server 2016 which has the geometry datum/projection of GDA1994 Zone 54 (EPSG:28354) and we wish to reproject/shift the spatial locations to ...
2
votes
0answers
388 views
Importing SHP to MSSQL using OGR2OGR?
I am trying to import a Shapefile which is in EPSG:4326 coordinates to MSSQL Database but its failing. I am following the below command:
ogr2ogr -f MSSQLSpatial -overwrite "MSSQL:server=...
1
vote
0answers
315 views
Parsing Geometry to Latitude, Longitude is failing
I have a table 'geocodes_raw' of locations with Latitude and Longitude values.
Loc_Nm || geocode_latitude || geocode_longitude
A || 43.43044 || -80.0910793
W || 43.9114523 ...
1
vote
1answer
392 views
Pulling Spatial Data in Geography Format From MS SQL Server 2008 Using ogr2ogr
Can the MSSQLSpatial driver support geography fields queried from MS SQL Server 2008 as spatial data?
Background
I have been using unixODBC on an Ubuntu 16.04 machine with ODBC Driver 17 for SQL ...
3
votes
1answer
1k views
SQL WKT draws well with geometry, but not with geography data type
I have a following piece of code
DECLARE @g geometry;
DECLARE @borders geography;
SET @g = geometry::STGeomFromText('SOME WKT', 0);
SET @borders = GEOGRAPHY::STGeomFromText(@g.MakeValid()....
2
votes
1answer
425 views
Query MSSQL geography data type against a bounding box
I'm currently storing geographic locations as data type geography in my MSSQL database and performing radius queries against it without issue.
Is it possible to query against it using a polygon ...
4
votes
0answers
1k views
When does WKT orientation matter for polygon and multipolygon in geography spatial (left/right hand rule, counter-clockwise, clockwise)
I've decide to use sql server on Azure for my gis database.
I'll be dealing with spatial Geography not Geometry.
While inserting some country boundaries using the spatial data in SQL Server, some ...
1
vote
0answers
127 views
SQL Server Routing or options
We are looking for a way using SQL Server spatial queries (or spatial library if needed) to do the following:
We have our own set of spatial data for custom roads (single line) and many point along ...
1
vote
1answer
642 views
Converting SQL geometry data queries into R spatial dataframes
I'm interested in querying data with a geometry field from SQL server and using it in R as a spatialPolygonsDataFrame.
I've seen plenty of examples of using rgdal to import a shapefile, but ...
3
votes
0answers
129 views
Using SQL intersect query?
I am trying to find the length of a polyline inside a polygon using this query:
declare @p geometry, @l geometry;
select @l = SHAPE FROM [Vz_DSM_Stationing].[dbo].[Route_Sections] where OBJECTID = 3;...
0
votes
0answers
40 views
Choosing table structure for MSSQL Server to render GeoSpatial Data on OpenLayers Map?
I have billions of public data sets.
I created MSSQL Database with the following table Structure:
ID:nvarchar,
Name:nvarchar,
Latitude:nvarchar,
Longitude:nvarchar,
Geometry:geometry
To represent on ...
1
vote
1answer
2k views
How to Cast geometry to Geography in SQL script in MS SQL Server?
I have a Table with some spatial Data in MS SQL Server. The datatype of the spatial column is geometry.
I'm trying to get the closest data from an input file using the following query:
DECLARE @g ...
2
votes
1answer
35 views
Why does the SRID of an ogr2ogr-imported MSSQLSpatial table change when using UID/PWD vs. trusted_connection?
I'm using python to run ogr2ogr commands to load SHP to MSSQLSpatial.
The command works fine when using trusted_connection, but when I use UID and PWD, the SRID is randomly changed to SRID:846 (while ...
1
vote
0answers
138 views
Reducing coordinate precision in MSSQL WKT using C#? [closed]
When you use the AsText() function on geometry objects in MSSQL Server 2008+ you get something like this:
"LINESTRING (8.8446177236171337 47.721989356913951, 8.844617919281065 47.721977134398223, 8....
0
votes
2answers
76 views
Add column to show intersection with another layer
I have a table containing a list of properties.
I have a successful query to return only those that intersect another layer.
However i want to return all the properties now and instead return a new ...
0
votes
0answers
189 views
ArcGIS Server and Microsoft SQL Server database connection
I have an ArcGIS Server and I need to register a Microsoft SQL Server database as a data storage. When I connect to database directly from ArcMap everything is fine, but when i'm trying to register a ...
5
votes
1answer
1k views
STContains always returns 0 when i query from table. But same data works when I manually create a polygon geometry object and select
I'm new to sql geometry. I've a table that stores polygon or multipolygon in a column. Below given a sample stringified row. Latitude, longitude, geom columns respectively
36.846642 -119.613123 ...
1
vote
1answer
47 views
Where do spatial indexes look for SRIDs?
I'm looking into optimizing the spatial indexes on some of our tables (stored in SQL Server 2008 R2), so I ran
exec sp_help_spatial_geometry_index
@tabname='myTable',
@indexname='...
2
votes
0answers
353 views
OGR2OGR EPSG:27700 Shapefile To EPSG:4326 MSSQL Table With Geography
I have been messing around with OGR2OGR for a while now trying to import a shapefile data into an MSSQL table. I am using the OGR2OGR executable that comes packaged with QGIS Wien (GDAL version 1.11.2)...
6
votes
3answers
1k views
How to create autoincrementing primary key with dynamic writer in FME
I'm writing 3 shapefiles to an MSSQL Spatial database with FME. I'm using a writer with dynamic settings to simplify future updates of the input files.
In order to create a spatial index with the ...
3
votes
0answers
494 views
Calculate lat/long as percentage distance between two points (MSSQL)
I've loaded the US road shapes into MSSQL. I've determined the query to locate the segment of road for a specific address. The segment contains a "linestring" from a series of points.
I'm trying to ...
2
votes
0answers
425 views
Import to PostGIS from QGIS (database sync)
I'm trying to write a simple Python script to load data stored in MSSQL to PostGIS. Idea is to load processing in QGIS and then run "importintopostgis" geoalgorithm : "Import into Postgis" from ...
2
votes
3answers
508 views
ogr2ogr mssqlspatial string imports as a single special character
When importing a shapefile (in this case a public census tract shapefile) into SQL Server 2016 using ogr2ogr, the string fields are set to a single special character. The geography and integer fields ...
1
vote
1answer
391 views
MSSQL Geography column - Sweref99
I have a table containing a LocationEast and LocationNorth columns(int).
LocationEast and LocationNorth are coordinates that follows the SWEREF99 standard.
According to this page, http://www.epsg-...
1
vote
0answers
105 views
QGIS 2.14.10 MS SQL Server 2008 Primary Key Issue
I've just spent the last hour wrestling with a spatial selection problem on an MS SQL Server layer in QGIS 2.14.10. The layer is a point layer and each time I click on a point every point in the layer ...
1
vote
0answers
529 views
How to use ogr2ogr to only add new features to an existing layer?
It seems like the correct usage is as follows:
ogr2ogr -update -append -lco SCHEMA=mySchema -f MSSQLSpatial "MSSQL:server=myServer;database=myDb;trusted_connection=yes;" "myShapeFile.shp"
But it is ...
1
vote
0answers
63 views
SQL Spatial Feature export
I managed to successfully import the spatial features from the MSSQL into QGIS and would like to know if the following is possible and how would I go about doing it:
Edit the attributes (without ...
