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
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. ...
user avatar
  • 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 ...
user avatar
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. ...
user avatar
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. ...
user avatar
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))), ...
user avatar
  • 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....
user avatar
  • 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 - ...
user avatar
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 ...
user avatar
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 '...
user avatar
  • 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, ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 --...
user avatar
  • 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 ...
user avatar
  • 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. &...
user avatar
  • 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....
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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)))...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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, ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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'...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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(...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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)...
user avatar
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 ...
user avatar
  • 41

1
2 3 4 5
33