Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
1,644
questions
2
votes
0
answers
18
views
Interpolated for every point in the raster space
I have here made a demo of my attempt turn points on a map into a with values raster then use St_interpolateraster and than send it back as geojson. It returns values and they are interpolated well. ...
- 51
0
votes
0
answers
13
views
Connect ArcGIS Desktop and SQL Server [closed]
I work for a natural resources organization doing salmon research. I'm fairly proficient with both SQL Server and ArcMap; but I'm better at SQL than I am GIS. We'll be migrating to ArcGIS Pro in the ...
- 1
4
votes
2
answers
177
views
Virtual layer that shows the union of multiple layers with same schema
I have a Layer Group surveyor with lots of layers (Shape files) with seemingly random names, and with identical schemas. I would like to combine them all to one layer; preferably with a virtual layer. ...
- 441
0
votes
0
answers
19
views
Spatial Join - Polygon vs Point - Is it possible matching output fields?
I have two feature classes in a geodatabase, one is a polygon or regular cells of a grid (Grid), the other one is a point feature (PointHabitat). A polygon or a cell can intersect one or more point. ...
0
votes
1
answer
31
views
Improve performance of startpoint query (ST_GEOMETRY)
I have a SDE.ST_GEOMETRY query in an Oracle 18c EGDB that gets the startpoint of polylines:
select
objectid,
sde.st_geomfromwkb(sde.st_asbinary(sde.st_startpoint(sde.st_geometryn(shape,1))), ...
- 754
0
votes
1
answer
22
views
Select SDE.ST_GEOMETRY as JSON text (using SQL)
I have a multipart M-enabled SDE.ST_GEOMETRY polyline FC (in an Oracle 18c EGDB):
Using SQL, I want to select the geometry as JSON text:
{"hasM":true,"paths":[[[0,5,0],[10,10,11....
- 754
4
votes
3
answers
96
views
Points which are beyond certain distance from multiple points
I have two tables - first is a list of positions (points) and second is a list of cities (cities). I am using PostGIS and can find all points outside 10nm of New York by using the SQL statement -
...
- 133
0
votes
2
answers
33
views
Loading SQL X/Y Table into QGIS
I'm new to QGIS (moving from ESRI).
I'm wanting to use an SQL table that has X and Y coordinate columns for viewing in QGIS as a layer. I've created and tested the ODBC connection to the database ok ...
2
votes
0
answers
25
views
Possible to use output data variable from Iterator in SQL query?
I am looking to use a value returned by an iterator as part of an SQL query but cannot figure out the right way to do this with an SQL expression:
I have a table of 7 text values (Cadastre) that the '...
- 21
1
vote
1
answer
29
views
Reviewing query from which Virtual Layer was originated in QGIS
I created a Virtual Layer by going in Layer > Add Layer > Add or Edit Virtual Layer. This new layer was created by building a query on another layer I had in my project.
Ho can I check, ...
- 671
1
vote
0
answers
20
views
Creating a Point with SRID - MariaDB
I have a table containing a longitude and latitude (as doubles) in MariaDB. What I'm trying to do is create a point with a specific SRID - 4326. The current way that I create a point is by using the ...
1
vote
1
answer
41
views
Count records from a Feature Class, where the combination of 2 columns match another Table(View)
I have difficulty formulating a good title for this problem.
Problem statement
I have the following 2 datasets, that are included into the same file local GDB. For these tables I want to find the ...
- 1,056
2
votes
1
answer
58
views
ST_Within is only working on selective rows
I have two tables (Table1, 10k rows, and Table2, 100k rows). Table1 contains latitude and longitude columns while Table2 contains a geometry column (representing a polygon). I wanted to merge these ...
- 23
0
votes
1
answer
24
views
Geo search MySQL table for rows that are within proximity
I have an sql table that has some data on retail locations with lat, long coordinates. My application queries the table to pull the data using python. Here's some sample data:
table1
id name lat ...
- 323
0
votes
1
answer
50
views
Is a GIS database necessary? Most efficient way? [closed]
I have been tasked with finding an efficient way to store data of thousands of addresses to compare to a broader set of addresses of all retail stores in America. Ideally, I would like the database to ...
3
votes
2
answers
59
views
Create a new table, sorted by st_area function in descending order
I have a very large table in postgis which has more than 10 million polygons and I wanted to create a function using this table for pg_tileserv but the problem is the size of table which takes too ...
- 41
2
votes
1
answer
65
views
Virtual-Layer/SQL-View: SQL to get list of all values in a group (SpatiaLite/GeoPackage) [closed]
I have a GeoPackage-Table and it's looking like that:
id data
-- ----
1 A
1 A
1 B
2 A
2 B
3 B
3 B
I need a virtual layer or a SQL-View that should look like that:
id data
--...
- 7,653
0
votes
0
answers
28
views
SQL expression to decision tree
I have an SQL expression with a lot of CASE WHEN. I'm trying to see if there is a simple way to take this expression, and turn it into a drawing decision tree.
This would be helpful to explain for ...
- 600
1
vote
1
answer
36
views
MapInfo SQL Query with Metacharacters?
I have +8 million edge in MapInfo. I want to select character+digit but i cant.
I tried querying as [a-z]+[0-9] but failed to use metacharacters in Mapinfo.
"New York 1. Street" is true.
&...
- 21
1
vote
1
answer
43
views
Variables in SQL expression in ArcPy script
I have a Python script with three parameters to collect user inputs via GetParameterAsText...
Buffer_Distance = arcpy.GetParameterAsText(0) # a string data type number (e.g. 40)
slp_min = arcpy....
- 43
0
votes
0
answers
55
views
Getting nearest city to point [duplicate]
UPDATE: This question was entered, but by mistake I re-asked in another post, so please consider this post as the one that answer this. This question should be closed and mark as duplicate.
I have a ...
- 159
2
votes
1
answer
66
views
Getting nearest city related to point in PostGIS
I have a table clusters with points.
CREATE TABLE public.clusters (
cluster_id int4 NULL,
punto public.geometry NULL,
cnt int8 NULL
);
INSERT INTO clusters ...
- 159
1
vote
0
answers
9
views
How do I project and merge MS SQL views into a feature class that can be published as a service?
I have three views (or enterprise geodatabase tables) in a SQL database. They each contain partial components of a complete dataset, but have distinct coordinate systems. I want to project and combine ...
- 11
0
votes
0
answers
22
views
Intersect points with polygons using SQL Server query from ArcGIS Enterprise Geodatabase
I have 2 databases in ArcGIS Enterprise Geodatabase in SQL Server. One database (DB_A) contains a feature class with points and the other database (DB_B) contains a feature class with polygons. I'd ...
- 271
2
votes
1
answer
228
views
Getting "No function matches the given name and argument types" when converting points to Polygons / Voronoi Diagram
I have these points
stored in my database. Now I am trying to use to create a Voronoi diagram with ST_VoronoiPolygons(). I have tried doing:
SELECT
(ST_DUMP(ST_VoronoiPolygons(ST_Collect(geog)))...
0
votes
0
answers
37
views
SQL query on array data in ArcGIS attribute table
I have an attribute table where courses offered by different colleges are in an array, I am trying to Query using ArcGIS query builder so as to return colleges offering a specific course within the ...
- 9
4
votes
1
answer
76
views
Grouping lines by name and determining their proportions using Virtual Layer
I have a line layer with hiking trails. The hiking trails are divided into their surfaces. So one hiking trail consist of parts with different surfaces.
I would like to use a virtual layer to group ...
- 7,653
2
votes
1
answer
56
views
QGIS: importing dataset based on zipcodes, not coordinates
for a current project I want to connect nation-wide PV-modules (photovoltaic modules) to power grid nodes via zip codes and their respective power demands. For this reason, I downloaded QGIS as I was ...
- 51
-1
votes
1
answer
39
views
SQL statement for count of different ranges [closed]
In QGIS, I want to make a SQL-statement which should return the counts of different year ranges e.g. in column 1 should be the total count for distinct values for all years, in column 2 1950-1990, in ...
- 851
0
votes
0
answers
36
views
Spatially Joining two tables
I have two tables in MariaDB. I am trying to join these two tables (Table 1 and Table 2). Table 1 contains Longitude and Latitude columns and Table 2 contains a MultiPolygonGeom column, which contains ...
- 23
0
votes
1
answer
59
views
Converting a WKT column to type Geom
I have the following table in my MariaDB database:
This table contains a column called 'coordinates' (type: LONGTEXT) with a wkt representation of either a MultiPolygon or a Polygon. I am attempting ...
4
votes
2
answers
38
views
How to specify the output of the previous algorithm as input to the QGIS "Execute SQL"?
I am trying to use the SQL Execute in the graph modeler, receiving as input the output of another algorithm. The problem is that I couldn't specify that the "input" in the FROM of the SQL ...
- 49
1
vote
1
answer
28
views
Representing a route that doubles back on itself in BigQuery with a LineString
I'm trying to represent routes in BigQuery, but running into issues for routes that double back on themselves. Consider the following LineString that proceeds from the southwest to the northeast of ...
- 133
1
vote
0
answers
39
views
QGIS virtual layer SQL Query Arrays
I have a polygon layer (A) with a field containing the farmers that use that polygon (the UUID of each farmer):
In each polygon feature can work from one to several farmers as it can be seen in the ...
- 53
1
vote
1
answer
46
views
QGIS Virtual Layer SQL Query sum if
In QGIS I have a polygon virtual layer (A) with 3 fields:
"Codes": Codes of farmers. Each farmer can be duplicated up to 5 times based on next field.
"Intensity": Farming ...
- 53
2
votes
1
answer
20
views
Deleting entries (duplicates) from a GPKG using OGR
I tried to delete duplicates from a GPKG file using ogr2ogr and a SQLite statement from SO:
ogr2ogr -sql "DELETE FROM layer WHERE rowid NOT IN (SELECT MIN(rowid) FROM layer GROUP BY ...
- 3,205
2
votes
2
answers
42
views
QGIS ST_Union polygon and table
In QGIS I have:
A polygon table (A) with farmed area. It has an UUID field.
A no geometry table (B) with farmers data. It has a UUID field.
A no geometry pivot table (C) that relates them both N-M, ...
- 53
2
votes
1
answer
88
views
Map not showing behind polygon
I have inserted polygon data in POSTGIS using an SQL query via dbeaver. However, when I view the value tab, only the polygon is showing without the map whereas when I am seeing Multipolygon in the ...
- 47
1
vote
0
answers
22
views
ArcGIS: restrict FeatureLayer SQL access based on token
I am working on a web app using the ArcGIS API for JavaScript that fetches a FeatureLayer from ArcGIS Online. The web app has users with many different permission roles and I want users to be able to ...
- 11
1
vote
0
answers
36
views
Use spatial query to load WFS data into QGIS
I tried to load a WFS into my QGIS project but the number and complexity of the polygon features are to high to handle for QGIS so that it throws an error. So I tried to add a spatial query to the ...
- 11
0
votes
0
answers
16
views
How to join two or more observation tables to one site table using SQL
So I'm working with joining several tables using SQL so that when the view is displayed on ArcMap, for one clicked on location, it will display all observations in the pop up attributes. Right now, it'...
- 69
0
votes
1
answer
76
views
Converting SQL queries from PostGIS to BigQuery
I have a table called drawn_items with columns “the_geom”, “fsrn_db” , & “area” in Carto2 which is queried by SQL. Having recreated the table in Carto 3, in order to use the query, I must ...
- 41
6
votes
1
answer
105
views
Actual PostGIS query execution time MUCH slower than expected
I've run into a performance problem from which I'm not sure how to move forward. I have a table with ~150M rows. And I have a query that in EXPLAIN has a cost of just cost=0.55..209.36, but in EXPLAIN ...
- 101
0
votes
1
answer
36
views
Clip a shapefile by a line border
I'm trying to generate a clipped file of a sample polygon geography (in this case, zip) and the U.S. coastline.
This is the query I currently have:
select
state_code,
zip_code,
st_union(st_dump(...
2
votes
0
answers
45
views
Memory efficient way of creating a large PostGIS table
I am trying to add the results of a query to PostGIS DB. My query converts a raster band into polygons. Although the original raster is quite big (~150GB, 12 bands) my below query runs reasonably fast ...
- 393
0
votes
1
answer
65
views
Query SQL x Virtual Layer x Joined Fields
I am facing the following situation.
I have a spatial layer which I did a JOIN tool with a data table. Now in my attributes window I see all the fields: the original ones and the ones from the joined ...
0
votes
1
answer
38
views
Use SQL on Layer in QGIS/SQL console in QGIS
There is the Field Calculator which has a lot of functionalities. Then there is Select by Expression which has a SQL Style Syntax.
However, is it possible to write plain SQL in QGIS on a Layer like a ...
- 3,017
0
votes
2
answers
83
views
Why doesn't ST_Intersects work with multiple rows of geom
I am new to PostGIS. I am trying to find the conditional intersection between two multi-polygons PostGIS tables (tab1 and tab2) when tab2 meets certain criteria (defined by row values of one of the ...
- 393
1
vote
1
answer
25
views
SQL with numeric data type in Carto
I have a Carto database with column "area" where data type is "number" or numeric.
The following SQL query is to select certain rows (cartodb_ids) and update the geometry (geojsons)...
- 41
1
vote
1
answer
63
views
Why does casting geometry to box2d give different precision from 2 databases
I have a postgresql 9.0 DB with postgis extension that I have migrated to another server running postgresql 11.13. Visual inspection of the tables shows matching data in raw form. E.g.:
When ...
- 41