Questions tagged [sql-server]
Microsoft SQL Server is a relational database server product by Microsoft. Its primary query languages are T-SQL and ANSI SQL.
0
votes
0answers
29 views
Precision difference MSSQL geometry vs PostGIS geometry [on hold]
For a school project I need to sync geometries betwee a MSSQL server and a PostGIS with a Python script (get geometry as WKT, create shapely polygons and compare with .equals).
Everything works fine ...
1
vote
0answers
10 views
arcpy.ArcSDESQLExecute with UPDATE and OUTPUT
Is there a way to get OUTPUT through arcpy.ArcSDESQLExecute? Whatever I do I get only True. When running UPDATE through MS SQL Server Management Studio I get OBJECTID of all updated features (rows):
...
0
votes
0answers
34 views
OGR2OGR -overwrite mssql table is appending instead
I am running
Command '['C:\\OSGeo4W\\bin\\ogr2ogr.exe', '-progress', '-skipfailures', '-overwrite', '-lco', 'UPLOAD_GEOM_FORMAT=wkt',
'-t_srs', 'EPSG:28354', '-f', 'MSSQLSpatial', 'MSSQL:Server=xxx;...
0
votes
1answer
14 views
MSSQL performance with Geography (point within polygon)
I have a query, below, that works - however its very slow, looping through roughly 24,000 points per second. This is a rather high end production DB server with MSSQL and SDE, and in other instances a ...
0
votes
1answer
21 views
ogr2ogr Warning 502: Field name 'yyy xx' contains invalid characters. 'yyy_xx' will be used instead
On MSSQL tables/views that have field names with spaces or starting with a number we get the warning 502 re: replacing invalid characters with a _
As we re-injest some of these files into MSSQL and ...
0
votes
1answer
18 views
ogr2ogr conversion failed when converting the varchar value to data type int
I keep getting this Error 1 when I run the following on some tables
ogr2ogr -skipfailures -f "MapInfo File" -lco overwrite=yes -lco launder=no -lco relaxedFieldNameMatch "D:\QG\CREFNO.tab" "MSSQL:...
0
votes
1answer
34 views
Uploading TAB data to MSSQL using ogr2ogr
I have been trying various methods to import a shapefile into MSSQL but keep getting
FAILURE: no source datasource provided.
ATTEMPTS
ogr2ogr -f "MSSQL:Server=xxx;Database=_GIS_Test;UID=yyy;PWD=...
1
vote
1answer
34 views
Converting MSSQL linked MapInfo Tab file to a shapefile using ogr2ogr
I have some tab files that are actually references to a view in a MS SQL Server table. It looks like
begin_metadata
"\IsReadOnly" = "FALSE"
"\MapInfo" = ""
"\MapInfo\TableID" = "055372be-523f-430a-...
0
votes
0answers
17 views
In QGIS3 graphical modeler can I access a mssql based view as an input?
Is it possible to set the input file of "Extract by Attribute" to an mssql based table view?
In the drop down to choose the input DB I don't see the loaded db view and don't see a way to connect one ...
0
votes
1answer
71 views
Database support in GE Smallworld?
What database engine can be used for GE Smallworld?
I have poor information but I read about Smallword proprietary VMDS.
Is it possible to use Oracle or SQL Server?
2
votes
0answers
20 views
T-SQL Invalid Geometry Created by Intersection
I have two sets of geometries (A and B) in SQL Server 2016. I want to determine the overlap areas of those geometries from set A that intersect set B.
Most of the time this code works:
SELECT a....
1
vote
1answer
46 views
Using data from SQL Table instead of File Geodatabase Table
Does anyone know of a method to use the table from SQL Server table using OS Authentication, instead of a File Geodatabase Table in ArcPy?
I have been using the below to reference the table
...
0
votes
0answers
40 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 ...
1
vote
1answer
50 views
Open a MSSQL Spatial View in QGIS
I have created a view in MSSQL Server 2012 that does a spatial join as below:
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS PID, DS.Dip_No, PB.SP_GEOMETRY
FROM dbo.Dipsites AS DS INNER JOIN
...
0
votes
0answers
23 views
export pymssql query to geodatabase table
I have thousands of databases in Microsoft SQL Server and I want the user to select the server and DB they want so I cannot use arcpy.ArcSDESQLExecute
I am trying to use pymssql to connect to the ...
1
vote
0answers
33 views
Load Raster from MS SQL Server into QGIS
Attempting to connect to an MS SQL database in QGIS.
Database connects fine, and all the vector items load correctly.
Rasters, however, appear as polygons, geometry column shows as FOOTPRINT, and ...
2
votes
1answer
65 views
How to access data from a registered SQL Server database in ArcGIS Server
Little bit of background on the problem: I have been working on a PHP application with an SQL Server backend. The application works through a web browser, but, I've recently been tasked with allowing ...
1
vote
0answers
79 views
Error QODBC driver not loaded when trying to connect to an MSSQL database from QGIS 3.4.3 (Ubuntu)
I have installed QGIS Desktop (latest one: 3.4.3 Madeira) along with the unixODBC-dev for SQL Server 2017 (following instructions here). I used sqlcmd from msql-tools and successfully connected to the ...
0
votes
0answers
26 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
37 views
QGIS 3.4.2 SQL data not displaying on map
When I connect to SQL Server and tick 'Only look in the geometry_columns metadata table' it speeds up the scan but then when I add the feature class to the map it only appears in the layers list and ...
0
votes
0answers
49 views
Adding ArcGIS Pro Query Layer?
I am attempting to add a query layer to ArcGIS Pro 2.1.2 via SQL Server.
The table consists of Ticket_ID, Latitude, Longitude. The query layer works if I only add the top 1000 rows. However, when I ...
0
votes
1answer
68 views
Create Database View with ST_Within Query
I see several other questions similar but none that apply directly to mine.
I am using ArcGIS Desktop 10.6.1 with SQL Server running my Enterprise Geodatabase.
I have a very large point feature ...
2
votes
0answers
34 views
Use PYQGIS to view MS SQL Server temporary table in QGIS
I'm working on a QGIS plugin to query a non spatial database held in MS SQL Server and to display the results of the query as a non spatial table in QGIS. I've tried the following:
create global ...
2
votes
0answers
82 views
From ESRI:103300 to EPSG:4326 in SQL Server
This question is more or less a sequel to Performance of joining tables on a geometry column with SQL Server
As explained in the above link, I have a shapefile that I upload to my SQL Server DB using ...
1
vote
0answers
44 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>...
1
vote
1answer
58 views
24412: Not valid because the interior of a polygon with rings (2) and (1) is not connected
I've got geography in sqlserver that is invalid. Running IsValidDetailed() I get the reason as
24412: Not valid because the interior of a polygon with rings (2) and (1) is not connected.
But I don'...
2
votes
1answer
65 views
Exporting SQL Server .bak files to a shapefile? [closed]
I have no experience using Microsoft SQL Server at all. Someone gave me a .bak file of their database and said that I can extract their spatial data that way. They said that they would not export them ...
0
votes
1answer
167 views
Performance of joining tables on a geometry column with SQL Server
I had a shapefile that I needed to integrate into my SQL Server DB. To do so, I used the very useful Shape2Sql.exe with which I chose 'Set SRID 4326' in the settings.
I now have a table that contains ...
6
votes
1answer
106 views
ArcGIS Pro missing records in attribute table from SQL Server?
I created a table to test ArcGIS Pro 2.2.3 with an Azure SQL Server. The structure of the table is simply an OBJECTID, a shape (Point) and a text field. The table contains a set of 2 points with ...
0
votes
0answers
28 views
Microsoft SQL Server - Multi-user solution
My team and I are currently trying to apply multi-user techniques in our projects. So far we have used an ArcGIS Enterprise build for this purpose but we quickly learned there are a few limitations. (...
0
votes
0answers
168 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 ...
0
votes
1answer
89 views
Identify EPSG in SQL Server
I have been trying to load a shapefile available at https://www.elections.on.ca/en/voting-in-ontario/electoral-district-shapefiles/open-use-data-product-licence-agreement/download-shapefiles.html to ...
0
votes
1answer
85 views
Connect to local Microsoft SQL Server Management Studio with QGIS
I am running SQL Server 2014 Management Studio and 2.18.16 version of QGIS. When I attempt Layer -> Add Layer -> SQL Server layer and click new:
Connection name: Local
Provider/DSN blank
Host: ...
1
vote
0answers
39 views
ArcGIS feature layer built of SQL View not visible
I am building a custom web app using the ArcGIS JS 4.8 API with an ArcGIS Web service and I have run into an issue.
The root cause that I am trying to fix is to do with features not being filtered ...
2
votes
1answer
79 views
Convert Shapefile from EPSG:5320 to 4326
I am trying to need Electoral District boundary data available at the below-mentioned link to SQL Server.
https://www.elections.on.ca/en/voting-in-ontario/electoral-district-shapefiles/open-use-data-...
3
votes
1answer
212 views
ArcGIS Pro cannot read SQL Server databases with “-” in their name
I am using the latest ArcGIS Pro 2.2.1 and trying to use a geospatial database that is hosted in an Azure SQL Server. The issue is that ArcGIS Pro fails to query databases that have "-" in their name.
...
0
votes
0answers
38 views
QGIS MSSQL datatable only querying one record
I made a successful connection to a local MSSQL Database where there are no spatial tables (only datatables).
I list the tables/views in the database (clicking on "Also list tables with no geometry" ...
1
vote
1answer
41 views
Stop ArcGIS 'Geodatabase Adminstration' Reporting Connection as 'DBO' Instead of My Username?
We are running ArcGIS 10.5.1 and MS SQL Server, and using an SDE connection file which is configured to use 'Operating System' credentials.
When most users connect, the 'Geodatabase Administration' ...
1
vote
0answers
32 views
SaaS for high performance geographic intersections?
I have a fairly simple set of requirements, but I'm struggling to find anything that will handle them "out of the box" at scale. This could be my ignorance with GIS terms, or that there is nothing.
...
1
vote
1answer
143 views
Unable to load layers from MSSQL PyQGIS
I have a plugin that loads layers from SQL Server 2008. It has worked before but when I reset my PC, i wasn't able to backup my plugins. I am left with an older copy. Below is my python code to load ...
0
votes
1answer
19 views
MSSQL SDE_GEOMETRYnnn Tables
In my database, I've found a table (SDE_GEOMETRY276) with permissions granted to one user. My first thought was the "276" was either a layer_id or table registration_id. Searching sde_layers and ...
3
votes
1answer
290 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()....
0
votes
1answer
113 views
ogr2ogr Export spatial data from SQL Server to Spatialite
I am trying to export some spatial data from SQL Server 2012 to Spatialite with the following command
ogr2ogr -f SQLite -update C:\tmp\Spatial.sqlite -nln "my_polygons" "MSSQL:server=host;database=...
1
vote
2answers
275 views
Using SqlGeometry.MakeValid to get a counter-clockwise polygon in SQL Server
The data I've loaded from a shapefile into a geometry column in SQL Server (using NetTopologySuite) is not producing valid GeoJSON when I export it because it doesn't follow the right-hand rule.
I ...
0
votes
0answers
15 views
Most efficient process for updating attributes on AGOL from MS SQL Server data source
We have a source system running MS SQL. There are attributes in this database we want to update for a feature class that we have in ArcGIS server and that have published from ArcGIS server into AGOL. ...
0
votes
0answers
276 views
SQL Server - ESPG:4326 data stored in Geometry column
It seems that the Microsoft SQL Server Geography data type is better suited for lat/lon because Geography "stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates."
...
0
votes
0answers
20 views
Determining the next checkpoint in a route
I have three tables in SQL Server: (1) Checkpoints, (2)Roads, and (3) Shipments
The Shipment has attributes for (1) last visited checkpoint, (2) next checkpoint to visit, and (3) destination ...
2
votes
1answer
151 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 ...
0
votes
0answers
21 views
QGIS MSSQL attribute table appears filtered when it isn't
EDIT: The MSSQL server version is 12.0.4522, the datatypes used are Geometry. I am not sure if it is topologically valid.
I originally had the same issue described and solved here,
Spatialite view ...
4
votes
0answers
559 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 ...
