Questions tagged [sql]

Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.

Filter by
Sorted by
Tagged with
0
votes
0answers
38 views

ST_TRANSFORM SDE Oracle does not convert the geometry

I try to convert a geometry from 31370 to 3857. But it does not work. The coordinates stay in 31370. DB: Oracle 11 ArcSDE: 10.4.1 The code: select sde.st_srid(shape) ,sde.st_x(shape) as ...
1
vote
1answer
24 views

pivot-table and group geometries

Postgresql11 - postgis2.4 Here is a table mytbl with points and an attribute var : geom | var pt a pt b pt1 a pt1 a pt1 b pt2 c ... I want to "rotate" the table like this : ...
3
votes
2answers
75 views

Distance between points inside LineString

I have a collection of geometries represented by a linestring. Each has at least two coordinates. Is it possible to write a SQL that instead of returning coordinates, return a distance between them. ...
3
votes
1answer
183 views

How do I use pre-existing Lat/Long data in a SQLite table with QGIS?

I have an application that stores its data in a SQLite database. I can't modify the application or database to add geometry extensions, but it does already have latitude and longitude columns for each ...
1
vote
0answers
29 views

Use selected features to generate WHERE clause in ArcGIS for Portal

I have a web map in Portal that has a point layer called Work Orders. I believe it's possible to manually select points in the layer by using the Web AppBuilder Select Widget. Using selected points, ...
4
votes
1answer
48 views

Aggregating multiple rasters within a polygon in PostGIS

I have several raster tables in PostGIS (four in fact but in this case let us assume there are just two) - same cell sizes, same alignment but with different types of value (e.g 3d_pot, 3d_area). ...
1
vote
1answer
26 views

Adding Encryption options to GeoServer SQL Server connection

I have set up an HTTPS connection between GeoServer and an Azure database using the SQL Server extension, however I would like to add encryption options to the connection string. Is this possible in ...
3
votes
1answer
86 views

Limit on Filter Query Stipulations?

I am novice mapper. I ran into some trouble with QGIS today when trying to add additional census tracts to a layer filter. The tract exists within the layer I'm filtering, but there seems to be a ...
0
votes
1answer
36 views

ArcMap SQL Trim Def query code

How can I get this ArcMap SQL Trim Def query code correct? I have a attribute field that looks like: LOC: R1, THEN A BIG LONG BUNCH OF WORDS or LOC: L10, THEN A BIG LONG BUNCH OF WORDS What I need ...
1
vote
1answer
75 views

Error while using ST_Line_Interpolate_Point: first argument isn't line

I've been running a scheduled task that contains some SQL code that has been running fine up until a few weeks ago. I don't believe any of the code had changed recently so I went through the debugger ...
1
vote
0answers
24 views

Unexpected query layer results: Why does unioning with DUAL fix it?

Simple Query: I have a query that connects to a table in a work order management database (Oracle 12c; non-GDB). select wo.wonum from maximo.workorder wo The query returns 428 records in ...
1
vote
0answers
86 views

Speed up PostGIS raster query

I have a simple PostGIS table as follows: \d rad; Table "public.rad" Column | Type | Collation | Nullable | Default ...
3
votes
1answer
115 views

Using ST_Segmentize to PostgreSQL table of segments?

I have a PostgreSQL table with straight lines (segments) that I need to subdivide in further segments so that none of them is more than 50 metres long. I am new using PostGIS (and SQL) and I'm ...
1
vote
0answers
17 views

Setting constraint so that new value in “Table A” must already exist in “Table B”, else return error? [closed]

I'm trying to set up some kind of constraint so that I can only enter values in "Table A", if that value exists in "Table B". I want this to act sort of like a selection list, where there are only a ...
5
votes
1answer
231 views

Concatenate and sequentially order unique groups of lines using SQL

I have a segmented line (road) dataset that potentially runs through multiple area polygons (localities). I want the road dataset that is grouped by the unique field (RoadMntnc) to have a concatenated ...
0
votes
0answers
68 views

Convert Polygon to MultiPolygon using features attributes

I'm trying to convert SHP files to GeoJson using ogr2ogr and that works well with Polygon. Now I'm struggling to convert multiple Polygon geometry features to a single one (MultiPolygon) for each ...
3
votes
1answer
67 views

Clustering geometry points, return a point's label if the point does not belong to a cluster?

I am clustering geometry points using the k-mean algorithm. The result is a list of clusters. Some clusters contain multiple points. Others only contain a single point. On the client side, we would ...
0
votes
0answers
35 views

Getting geodatabase to SQL Server

I am currently attempting to load in 1000 data sets I have in 1000 different tables. I need to do this in Microsoft SQL Server. My data sources are either in Excel or geodatabases. The plan is to ...
5
votes
1answer
269 views

Getting the line middle point with SQL in QGIS

Using SQL in a Virtual Layer is it possible to get the midpoint of a line on the line? I have tried Finding middle point (midpoint) of line in QGIS? but got an error I also tried SELECT road, ...
0
votes
1answer
41 views

Loop through geodatabases and list feature classes [closed]

Trying to find a way to use Python or SQL to loop through geodatabases and print the feature classes. I'm not sure what the difference between while and for loops are; I'm brand new to scripting. A ...
1
vote
1answer
42 views

Creating an SQL expression and writing/reading it to/from a file

I'm working on a way to take the number for projects that are marked as active in a spreadsheet and put that into a SQL expression that I can use to select from. So far I've got this dirname = os....
0
votes
0answers
109 views

From SQL database to pandas dataframe to numpy array to ESRI Table

End goal: Join data pulled from a SQL database to three different feature classes in a ESRI geodatabase. Efforts: Using PYODBC, I've connected to the database and dumped the data into a Pandas ...
1
vote
1answer
59 views

PostGIS: syntax-basics for function

I am a completely new in PostGIS. I tried to solve a problem I had in QGIS (see Simplify multiple lines: create central axis?). I just need to know how the correct syntax works to use the function ...
1
vote
0answers
92 views

“Driver not loaded Driver not loaded” when trying to connect to MSSQL database in QGIS 3.8 on MacOS

When trying to create a new MSSQL Connection, I get an error saying: Error opening connection: Driver not loaded Driver not loaded. I have tried both QGIS 3.8.0 and the newest pre-compiled QGIS 3.8.1 ...
1
vote
1answer
34 views

Measuring area covered by linestring

I have a trajectory given by GPS which I store as a ST_LineString() and I'd like to know the area covered by the line string. I know the "width" of the trajectory, but I'm not sure how to approach the ...
0
votes
0answers
29 views

Use custom SQL-functions within a WFS stored query (filter)

I want to set up a WFS (GeoServer) returning features which are matching a (given) term / name in a phonetic way by using a stored Query. I've got a SQL function which can be used as an operator for ...
1
vote
1answer
40 views

Find a point in a polygon

How can I find all points in a polygon pass in queries: My column coord is a geometry point example, pseudo-code. select a.* from "table" as a where ST_WITHIN(a.coord, [lng1 lat1, lng2 lat2, lng3 ...
0
votes
0answers
32 views

SQL Sort data spatially using Peano method

I have to keep going back to ArcGIS to sort a dataset spatially using the PEANO method that's described here - http://desktop.arcgis.com/en/arcmap/latest/tools/data-management-toolbox/how-sort-works....
0
votes
1answer
27 views

Make Query Layer only returns first row of each table

I have been trying to conduct an arcpy.MakeQueryLayer_management and it is only returning the first two values from each table in the SELECT. When I run the same SELECT statement in Oracle SQL ...
4
votes
1answer
77 views

Using a virtual layer, display polygons based on location of another polygon layer

QGIS 3.4 I have a .gml file of every freehold title in a city outlined red below. I want to create a virtual layer of the freeholds which are contained within the blue-green isochrones. Even if a ...
0
votes
2answers
99 views

Get line length using SQL in QGIS

I have a line dataset that has road names and different surface types along different segments of the road. I want to calculate the total length of each surface type for each road. I've been able to ...
2
votes
1answer
173 views

QGIS equivalent to Mapinfo SQL qery

To run a simple interest query in Mapinfo I would use Select * from _1_10000_map_sheet_key_map, Carpentaria_Shire where _1_10000_map_sheet_key_map.Obj Intersects Carpentaria_Shire.Obj into Selection ...
1
vote
1answer
59 views

Extracting Oracle geometry with FME's SQL Executor transformer

I am wondering why my SQL executor can't extract the geometry from my Oracle database. The geometry are polygons. I added the 'SHAPE' column to the query in the executor, and I exposed the attribute ...
0
votes
0answers
94 views

Query returning multiple vehicle_ids when it should only return the top 1

Using ArcGIS Desktop 10.5.1 I have a point featureclass in an Microsoft SQL Server 2012 Enterprise Geodatabase (ArcGIS Server 10.5) that holds MDS (mobility ride share scooter) data. The feature ...
0
votes
1answer
24 views

Using features of CARTO Builder to dynamically calculate the average of selected columns

I am trying to find a simple way to calculate the average of selected columns (avg per row) dynamically in CARTO, using only the pre implemented features of CARTO Builder so that it is as user-...
1
vote
1answer
66 views

GeoServer query with GeoJSON

I'm trying to serve a view of my table after passing a GeoJSON polygon. Te idea is get all the points inside my polygon passed as GeoJSON. For this, I've created new SQL view, and a query: select ...
1
vote
1answer
87 views

Seeking PyQGIS equivalent to Geopandas or SQL?

Using sql "DBpop2016" = maximum("DBpop2016" , group_by:= "DAUID") OR geopandas print (data.groupby('DAUID') ['DBUID','DBpop2016'].max()) I can select the single DB (of which there are several) ...
0
votes
0answers
57 views

Filter and search large WFS in QGIS

I'm using a large WFS layer that should be searched in such a way that it's filtered by three columns before loading into QGIS. I know that QGIS has a filter option, but I'm wondering if there is a ...
1
vote
1answer
59 views

Changing SQL syntax in QGIS Virtual Layers

I am experiencing difficulties with SQL syntax in a Virtual Layer. I can do the simplest query form such as: SELECT * FROM mytable and this displays on my canvas. However, if I try to name fields ...
1
vote
2answers
76 views

Wrong result using virtual layer sum with join and group by

I am trying to join the grouped sum of two layers using virutal layers in QGIS 3.2. My query looks like: select nvewi.zone as "Zone-Type", sum(nvewi.bev) as "Inhabitants Isochrones", sum(nvewp.bev) ...
1
vote
1answer
126 views

PostGIS- convert points to polygon based on multiple shared attributes

I have a PostgreSQL PostGIS point table (not multi-points). I want to convert the points to polygons (using convex-hull?) grouping the points by shared attributes from 2 columns. In this example, '...
3
votes
1answer
49 views

Select the smallest value from a selection using ArcMap SQL selection

I am trying to select the smallest Directionality value based on a selection of two Workingspace values: In the above picture, there are two "Jeffery's" selected, I want to then select the Jeffery ...
0
votes
1answer
139 views

Quering out multiple values in MapInfo SQL Query

I am trying to filter out around 1000 rows and delete them in MapInfo as they are no longer required in the data set. If there was a smaller quantity then I would use an SQL Query but it has its ...
1
vote
0answers
36 views

Trigger on PostGIS line layer occurs twice [closed]

Using this tutorial, I've established a SQL trigger in order to track changes on a PostGIS layer. It works great for a point layer. However, using the script below, it causes problems with line layer, ...
1
vote
0answers
33 views

Updating spatial table in Oracle database with SQL query in PyQGIS?

I am trying to use the Python Console within QGIS 3.4 to update a spatial table in an Oracle database. The goal is to update all values in the field DTM_ELEV in the Oracle table with values from a ...
2
votes
1answer
120 views

QGIS Virtual Layer Join SQLite/ Spatialite

I am attempting to recreate a flowmap workflow from @underdark The first step involves joining a non-spatial edges layer to a spatial nodes layer: SELECT StartID, DestID, Weight, make_line(a....
0
votes
0answers
29 views

Dynamic SQL Expression Parameter in Model Builder tool (Arcmap 10.3)

I have a working model in Arcmap 10.3 that creates a feature class data from CSV file, queries the data according to a SQL Expression, and then conducts many spatial joins. The model works as ...
0
votes
0answers
22 views

Login form for loding layer from MS SQL Server

I created database and featureclass and table in SQL server. I loaded layer in QGIS, my UI editing and veiw from, designed by qt designer and saved to QGIS project. Now I need to create login form: ...
1
vote
0answers
65 views

Creating virtual layer or view table from original table using pivot table on QGIS?

I have a table and I need to produce a virtual layer with pivot or crosstable command. Is it possible on QGIS 3.6 or 3.8? My original sql is: select cd_setor, Name, Path_relativo from Fotos order ...
3
votes
0answers
196 views

Open GPKG, query layers and save to new GPKG [closed]

I have recently started to use SQL and geopackages queries in R. I know how to open it, access the individual layers (geometry and tables), and select and process individual layers. But, I don't know ...