Questions tagged [sql-server-spatial]
Microsoft SQL spatial database
141
questions
1
vote
1
answer
40
views
Does QGIS import SQL Server data or query it?
I have a SQL Server DB with about 200GB of various datapoints stored on server.
When I use this datasource in QGIS from my PC would all the data be imported?
I.e do I need to make sure I have 200GB ...
- 11
0
votes
0
answers
27
views
Can I delete and recreate Feature Classes with same name?
We have a batch job that exports GIS data from a central database, classifies and segregates it, and imports it into a different spatial database on a different server.
I am being asked to modify this ...
- 65
0
votes
0
answers
30
views
QGIS Server doesn't connect to MSSQL layers
I tried to connect to MSSQL from QGIS Desktop 3.16, then I created a project with a layer from that connection and a layer from a gpkg and I saved the qgs file. Then, I followed this guide to install ...
- 1
3
votes
1
answer
45
views
MSSQL STIntersection Results in COMException
Using MS SQL Server 2019 and running the following query results in the COMException displayed below.
DECLARE @Geom1 AS GEOMETRY
DECLARE @Geom2 AS GEOMETRY
SET @Geom1 = GEOMETRY::...
- 106
1
vote
2
answers
72
views
Combine different geometry types in single layer in MapServer- is it possible?
I'm trying to display features with different geometry types in single LAYER. MapServer version 7.0.1 (MS4W 3.1.4). The data source is MS SQL spatial database table with geometry column, containing ...
- 131
0
votes
0
answers
47
views
SQL Server Get Point With Point, Distance and Azimuth
I am looking for a way to mimic PostGIS' ST_Project or Turf.js' destination in SQL Server.
What I want to do is give the function a point, a distance and either bearing or azimuth and get back a point ...
- 2,597
0
votes
1
answer
16
views
Create a Clone of existing Spatial DB on SQL Server instance
I have a spatial database on a SQL Server Instance. I want to create a copy of it in the same instance. I tried the following approaches:
Backing up the existing XYZ DB and restoring it with a new ...
- 65
1
vote
1
answer
233
views
STContains, STIntersects and STWithin return wrong result for geography loaded from WKT
I'm trying to determin whether a point is within a polygon. I created a polygon a two points with this tool: https://clydedacruz.github.io/openstreetmap-wkt-playground/
Polygon:
POLYGON((18....
- 111
0
votes
0
answers
20
views
Populate Geography data in SQL Server Management Studio GUI
Is there a specific syntax to use to populate a Geography type field in SQL Server Management Studio (SSMS). The below screenshot shows the SSMS GUI and the location field is of Geography type.
I have ...
- 11
4
votes
2
answers
405
views
Speed difference MS SQL Server 2019 to PostgreSQL/PostGIS 12 for spatial data
I imported a shapefile with 3000 polygons in UTM 25832 into both databases MS SQL Server 2019 and PostgreSQL/PostGIS. Then I calculated a buffer of 1000 meters around these geometries and transferred ...
- 519
0
votes
0
answers
121
views
Loading geometry point tables in SQL Server Spatial
I need to load point data in SQL Server Spatial.
Similar to this post but I really don't know how to get XY data to be accessible by SQL
I want to do what was done in the initial part of this post (...
- 23
0
votes
0
answers
60
views
Convert a SQL Server table to a shapefile with projection info
I've been using ogr2ogr to write out shapefiles from my SQL Server spatial map layers. When I run the command it delivers the .shp, .shx, and the .dbf but no prj file. Is there a way to get ogr2ogr ...
- 437
1
vote
0
answers
96
views
Converting non-simple geometries to simple so ArcGIS Pro can display and process them
In SQL Server 2016 I'm using GEOMETRY::UnionAggregate to dissolve some polygons into larger ones. Here is a simplified example of my select:
select ta.TRANUM, GEOMETRY::UnionAggregate(pp.SHAPE) SHAPE ...
- 2,284
2
votes
1
answer
103
views
Can not load spatial data from MSSQL on Linux
I have a table on the MS SQL server. I am trying to import the data in R using the rgdal package. I can import the table on Windows without any problems using the following code:
readOGR(dsn = "...
- 131
1
vote
0
answers
140
views
How to calculate a line from a position and bearing in SQL Server 2012 (Spatial)
Currently I've a table filled with measurements from a certain point. All the rows contain a location in Lat/Lon, a bearing in degrees and some other data containing sensor updates etc. At this moment ...
- 11
4
votes
1
answer
3k
views
Create a bounding box from a lat/long
I'm trying to create a bounding box from a simple Lat/Long, given an increase size in metres.
For example, create a bounding box from X/Y which goes outwards 5000m at the NE / SW corner. (yes, this ...
- 1,783
0
votes
0
answers
162
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" &...
- 1
0
votes
1
answer
96
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 ...
- 23
1
vote
1
answer
96
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 ...
- 33
2
votes
1
answer
700
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 ...
- 296
3
votes
2
answers
1k
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 ...
- 31
2
votes
2
answers
1k
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 ...
- 21
0
votes
1
answer
102
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 ...
- 783
1
vote
1
answer
172
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 ...
- 183
0
votes
0
answers
91
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. ...
- 637
1
vote
0
answers
109
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 ...
- 11
1
vote
1
answer
192
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 ...
- 814
0
votes
1
answer
212
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 ...
- 901
1
vote
0
answers
50
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 ...
- 11
1
vote
1
answer
413
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 ...
- 296
2
votes
1
answer
207
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 ...
- 21
1
vote
0
answers
450
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 ...
- 11
3
votes
1
answer
207
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.
- 81
0
votes
0
answers
55
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")
...
- 101
1
vote
0
answers
64
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
0
answers
396
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/...
- 3,508
6
votes
1
answer
656
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 ...
- 61
2
votes
0
answers
746
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=...
- 413
1
vote
0
answers
381
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 ...
- 65
1
vote
1
answer
794
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 ...
- 11
3
votes
1
answer
2k
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()....
- 131
2
votes
2
answers
991
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 ...
- 121
6
votes
0
answers
3k
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 ...
- 221
2
votes
0
answers
174
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 ...
- 21
2
votes
1
answer
1k
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 ...
- 437
3
votes
0
answers
151
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;
...
- 31
0
votes
0
answers
56
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 ...
- 195
4
votes
1
answer
5k
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 ...
- 39.8k
2
votes
1
answer
56
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 ...
2
votes
0
answers
212
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....
- 296