Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
1,260
questions
3
votes
1answer
50 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
193 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
48 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
39 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
29 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
192 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
23 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
40 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
27 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 ...
0
votes
1answer
50 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
21 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
27 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
21 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
38 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
21 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
25 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 ...
3
votes
1answer
63 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
63 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
73 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
36 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
91 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
13 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
34 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
73 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
37 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
45 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
37 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
56 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
45 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
46 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
34 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
17 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
69 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
14 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
21 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
46 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
79 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 ...
0
votes
1answer
51 views
How to fix this Heatmap script from failing SQL expression (integer/string?) using arcpy (ArcMap 10.5)?
I have a heatmap script that will eventually print 10 PDF maps. At the moment it is failing at the SQL expression in the Make Feature Layer right before Kernel Density Raster creation because the ...
1
vote
0answers
32 views
Select features of a shape which are not in another shape
I have a shape with some features (let's say 100) and a second shape with some of this features (50) buffered (5 meters inside buffer). I want to select and write in a new shape every feature of the ...
1
vote
0answers
41 views
Determining which latitude and longitude values fall within township/district/polygons
Is it possible to export the boundaries of one layer, as well as the latitude and longitude falling within each boundary, from a program/by programming?
I seem to have gotten fairly close to a ...
0
votes
1answer
31 views
Datatype changed to qlonglong from integer when export to shp and SQL
I have a view that basis on the table 'mytable' with these columns:
The view created as
CREATE or REPLACE VIEW viewbox.myview AS
SELECT mytable.h AS height,
mytable.geom
FROM mytable;
with columns
...
1
vote
1answer
62 views
SQL: How to union polygon geometries from a single table that have the same id and certain type?
Essentially, the table looks like this (geometries are mostly unique, and shouldn't overlap, but may overlap a little):
id sub-id type Geometry
123456 1 ordinary ...
2
votes
1answer
54 views
Merging two different geometry layers into one (SQL, GeoPackage)
I have a point layer ('points') and a polygon layer ('multipolygons') . I want to load the points layer combined with the centroids of the polygon layer.
Im using GeoPackage and tryed to go a SQL ...
0
votes
1answer
46 views
Improve Performance of ST_DWithin - H2GIS
I have a table seg_point, that I create like this:
CREATE TABLE seg_point (id INT AUTO_INCREMENT PRIMARY KEY, segmentid TEXT, geom GEOMETRY);
And I'm trying to improve the performance of this query ...
2
votes
2answers
38 views
FME-SQL where ID in id.list
I extracted and transformed worksite records from a Oracle dB and stored them in a PostGIS dB. There are about 700 worksite records having a unique ID.
I need to extract additional worksites from the ...
0
votes
1answer
144 views
Error: Geometry constraint geom-type or SRID not allowed. How to ensure the correct geom-type?
Following the top answer on a previous question about storing geometries in a spatialite database, I am trying to update a spatialite table using:
engine.execute("SELECT AddGeometryColumn('your_table'...
3
votes
1answer
80 views
Performance of arcpy.Exists() in SQL Server enterprise gdb environment
While iterating on a list containing table names,
I used arcpy.Exists() to check if a table/featureclass exists.
This action is very time consuming.
I've ended up using a database approach select ...
2
votes
1answer
54 views
PostgreSQL - Array of overlapping Polygon Ids
I have a database table (overlay) that has columns, id::int, intersections::int array, geom::polygon. I have another table (images) that has id and geom columns. I am trying to populate the ...
-1
votes
1answer
25 views
How to get points around one point buffer area in a table [closed]
I have many points in a table. I want to show points near one point. Buffer area can be 10 or 100 in SQL Server using SQL query.
