Questions tagged [sql]
Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.
1
vote
2answers
106 views
Using PostGIS to find which county in California has most settlements in forest?
Using PostGIS how do I find which county in California has the most settlements in the forest?
I have point settlements settlements_CA, polygon forests forests_CA and polygon counties counties_CA in ...
0
votes
0answers
24 views
Speeding up Oracle Spatial intersection query and indexes
I have about 300 000 records (worksites) in a table (S) with id (id_s), start_date, end_date (and in some cases an authorisation date - AUTH_START).
I have a second table containing the id of 500 of ...
-2
votes
0answers
13 views
"Errors found in your SQL query. Fix them before continuing.” [closed]
When I connect my data set from an external .xls file I get the error message:
Errors found in your SQL query. Fix them before continuing.
Does anyone know what this means?
1
vote
0answers
44 views
AGOL & SQL database: Is ArcGIS for Server necessary?
A client is requesting we come up with a Python script to have an AGOL feature layer and a SQL Server database containing similar information be able to read/write to each other without having to use ...
0
votes
2answers
45 views
How to delete boundary lines of split shapefile tiles with SQL of Virtual Layer
I have the split shapefiles like same size tiles.
I want to show these merged on QGIS.
So, I tried to use Virtual Layer like following image.
And, I wrote following SQL:
select * from north
UNION
...
1
vote
1answer
35 views
Execute Oracle stored procedure from ArcSDESQLExecute
I'm attempting to execute an Oracle 12c stored procedure using ArcSDESQLExecute:
import arcpy
egdb_conn = arcpy.ArcSDESQLExecute(r"Database Connections\con1.sde")
sql = '''execute update_sle (...
1
vote
2answers
66 views
PostGIS intersects and case function
Hi i've been setting up a script to assign values depending on one type of polygon intersecting with another polygon. I have two feature tables - one with building footprints and one with polygons ...
0
votes
1answer
24 views
SQL expression to select specific month across years
What is the ArcGIS SQL expression to select all rows containing the month of May across several years?
Desired records highlighted in Excel:
The source table is in a file geodatabase and I'm using ...
0
votes
1answer
32 views
Using an SQL Query to skip or run a code in ArcPy/ Python
Using ArcMap 10.4 and Python I am trying to run a code (arcpy.ExportReport) on a bunch of different frequency tables. Which I have completed the only problem is that if I have a table that does not ...
3
votes
1answer
66 views
Extending a segment in QGIS to check intersection with a polygon
I am trying to find the adress points (red dots) located in front of an obstacle (red polygon). The street is green and the pink line represents the street face (Si).
This is what I have so far, but ...
0
votes
1answer
37 views
Altering SQL View GeoServer from application
I am making an application and I want to access a lot of weather data.
Now I want to use the SQL View option to let the WMS layer know which timestamp to use and then it shows me all the data from ...
2
votes
2answers
61 views
Calculating point layer values aggregated by sub-polygons in QGIS 2
I simply have
a polygon layer BereichBerechnung with a field "Are_Number"
a point layer EW2017 with a field "EWjeAdr"
In QGIS with a Virtual Layer, I want to calculate the sum of the field "EWjeAdr" ...
0
votes
0answers
64 views
ArcGIS vs QGIS vs PostGIS projection issue
Windows 10, ArcGIS Pro, QGIS 2.18, pgAdmin 4-postgreSQL
I have two (sample area) PostGIS tables in a PostgreSQL database:
building_polys (SRID: 27700) -OS MastermapTopo
address_points (SRID: 4258) -...
1
vote
1answer
39 views
Efficient intersect query for virtual layers in QGIS
I have 3 tables/shapes as follows:
ADRR (adress points)
ADRR_ID | ST_ID (street surface id) | point geometry
STREET
ST_ID | ST_NAME (street name) | polygon geometry
Obstacles
ID_OBS | ...
0
votes
2answers
46 views
Automating Select by location using ArcPy and where_clause?
I have used the code in Coding python loop to select features of polygon layer than are completely contained in group of 11 layers using ArcPy? to automate select by location analysis using ArcPy.
I ...
2
votes
1answer
52 views
Select layer by location in PostgreSQL
I have a footprint of some buildings (see the picture).
The selected buildings have an area below 30 sqm.
As you can in the picture see some buildings share almost borders with a building bigger ...
3
votes
1answer
83 views
What is the metric of the radius PostGIS query?
I am trying to make a query (using raw query in sequelize orm for a PostgreSQL with PostGIS database) for a radius of a certain 'maxDistance' radius (preferably in meters):
const maxDistance = ...
-2
votes
1answer
35 views
Select the parts of a line that are within an area in PostGIS [closed]
In my project I have a road network and many administrative boundaries. My goal is to select the roads that intersect a concrete district but extracting only the parts of the roads that are within ...
0
votes
0answers
38 views
Clip all layers of a GeoPackage (GPKG) in one step
I would like to clip all layers of a GPKG-File to a given extend (canvas, mask layer, rectangle...) in one step. I could use clipping in batch mode but isn't there a nicer solution doing this in one ...
1
vote
1answer
24 views
Transform CRS for all geometry objects in SQL-Database in one step [closed]
I would like to easily change the CRS for all my geometry objects in a PostGIS/SpaitaLite/GPKG database in one step. In my case, all objects are in EPSG:4326 and I want them in EPSG:3044
1
vote
0answers
29 views
PostgreSQL Views with geometry on QGIS
Not entirely sure what kind of rules does QGIS need to consume SQL views
So I've had some success in using an id column and a geometry column but now that I'm doing something a bit more complex, it's ...
1
vote
1answer
27 views
Run query between PostGIS layer and Shapefile layer with DB Manager
Is it possible to use the DB Manager in QGIS (I am using version 3.4) to run a SQL query between a project layer (e.g. shapefile) and a PostGIS layer?
For example, I have a polygon shapefile in my ...
0
votes
1answer
37 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 ...
0
votes
1answer
38 views
Querying geometry in perimeter or square with SpatiaLite?
How can I query geometry in a perimeter or a square with SpatiaLite?
CREATE TABLE points (id INTEGER,geom GEOMETRY)
insert into points values (897, GeomFromText('POINT(40.0 3.0)'))
I've tried ...
2
votes
2answers
94 views
Renaming duplicates in PostGIS?
I have a table with a few columns (id, col2, col3, geom) in a PostgreSQL/PostGIS database.
I want to find all duplicates (or more) from "col3" having that they share value in "col2".
The next step ...
3
votes
3answers
139 views
Draw line to next feature with distance as label (dynamic)
I would like to dynamically draw the shortest line to the next object (Geometry Generator?).
Example: I have a network-layer (streets) and a point-layer. I know the distance from every point to the ...
3
votes
1answer
51 views
How to flatten spatially equal polygons in PostGIS?
I have the following dataset of polygons. All the pieces are broken based on their borders. I added some transparency to better understand what's going on. The pieces that have stronger colors, means ...
0
votes
2answers
31 views
Clarification on Oracle sdo_gtype
I need some clarification on oracle sdo_gtype 4402. I understand that sdo_gtype is codded as DLTT. D: Dimension L : linear reference T T: TT identifies the geometry type. It's clear to me that first 4 ...
0
votes
0answers
27 views
Query Feature Class from Oracle table with no geometry column
Using ArcMap 10.4.1 I am trying to create a query layer from an Oracle table (Oracle info at bottom). The table has no geometry column, but has x and y coordinates as double and I am trying to create ...
5
votes
1answer
46 views
Getting WHERE statement to work in v.generalize?
I have a shape (its topology is terrible but I don't think it's the problem). I'm trying to simplify it to reduced it's size using v.generalise. I succeeded at reducing the whole shape but I ...
0
votes
0answers
11 views
Record Local Authority a DB loaded coordinate falls within
I have a set of coordinates in SQL Server and I have a column with the geometry.
I have loaded these into QGIS, and also a layer of the local government regions.
I wish to save the name of the ...
2
votes
2answers
107 views
Why is creating features with FALSE boolean fields in QGIS and Postgres failing when the fields have default values?
I'm having problems with adding rows that have boolean columns to Postgres tables from QGIS. In a nutshell, shapes with boolean fields can only be created when the booleans are set to true (updates ...
10
votes
2answers
170 views
How to recursively loop through parent polygons intersections to get smallest (child) polygons with no overlaps?
I'm struggling with a problem for a couple of days and realized many people also get stuck when the topic is intersections in PostGIS (v2.5).
That's why I decided to ask for a more detailed and ...
0
votes
0answers
36 views
SQL Query OSM road types in a bounding box
I have a basic command that works to query all planet_osm_line features within a bounding box (running a PostgreSQL db of the entire OSM planet file)
SELECT *
FROM planet_osm_line
WHERE ...
-1
votes
0answers
71 views
Improving performance of PostGIS equivalent to ArcGIS Identity tool?
My goal is to implement ArcGIS's identity tool in PostGIS as part of an effort to port an ArcGIS model builder model to PostGIS on a linux environment. The model by itself only uses "Select Layer By ...
4
votes
1answer
58 views
Postgis spatial index not being used
I have a query to calculate how many crimes (of a certain type) are rural and urban:
select st_intersects(c.wkb_geometry,u.wkb_geometry) as ur,count(c.*)
from crime.street_crime c, meridian.urban ...
2
votes
2answers
47 views
Get single part polygon by their intersection in PostGIS
I have a table in PostGIS that has polygons overlapping each other. (see below an example of 4 overlapping polygons )
Someone could give me an idea, what PostGIS function I can use that give me ...
1
vote
2answers
105 views
How get intersection of polygons in the same table by PostGIS?
I have a single table of over 1000 polygons in postgreSQL (with PostGIS), and I have a field "evaluation_type" that only receives two values, either 'good'(green) or 'bad'(red), as you can see in the ...
2
votes
0answers
38 views
postgis and st_intersects ; fail to compare a table to itself with a left join
postgresql 9.6, postgis 2.3
Following this Select features that do NOT intersect in PostGIS, I would like to compare one table to itself, to keep all records from table p1, even and above all those ...
1
vote
1answer
27 views
Wildcard SQL in ArcGIS REST API
I'm querying the ArcGIS REST API and I'm trying to write a request that includes a wildcard. The normal request (without wildcard) looks like this:
https://serviciosgis.catastrobogota.gov.co/arcgis/...
0
votes
2answers
68 views
Show MapBasic Window equivalent in QGIS, e.g. Python Window
Is there a Show/Hide MapBasic Window (or equivalent) in QGIS such as in MapInfo?
I would like to learn SQL expressions parsing, or a Python Window viewer?
2
votes
1answer
43 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 ...
1
vote
1answer
450 views
Multiple markers on Google Maps using JavaScript, PHP and SQL
I am trying to query the MySQL database that has a table with firstname, longitude, latitude in it.
I want to get the longitude and latitude and display them as multiple markers with the firstname as ...
3
votes
1answer
48 views
Getting the distance traveled across road segments
so I have a query that takes an encoded polyline, segmentizes that polyline into smaller pieces, and matches each point on that segmentized line to a "road segment" from a separate table in my ...
6
votes
3answers
348 views
How to Fill blank Fields with other column QGIS
I have two columns of the same data and I want to unite
for example I have the column "pole_type" and "pole_type_"
and sometimes one has values and some times the other
like seen in the photo
I ...
1
vote
1answer
61 views
Split Polygon into separate polygons using a table of individual lines
I have been trying for a while but have not been able to split a polygon at multiple locations, using a table of lines, to get what is on the right side of the above image. The best I can achieve is ...
1
vote
2answers
38 views
Update polygon layer with summarized areas from other polygon layer
I use QGIS 3.2.3. I have two polygon layers "municipalities" and "landuse".
I want to update municipalities with the summarized area off landusepolygons within every municipality. landuse polygons do,...
0
votes
0answers
23 views
MS SQL Server STEnvelope() performance
Using MS SQL Server 2012, when we write a query with
select GEOM.STEnvelope() from table
where table holds approx. 4000 native SQL Server line geometries of type geometry, with 2/6/180 points min/...
1
vote
2answers
115 views
PostgreSQL select value where '100%' using bigint
I am trying to select data from a bigint column in PgAdmin using SQL:
SELECT FROM public.addressbaseplus WHERE uprn LIKE '100%'
However, I am getting the following error:
ERROR: operator does not ...
0
votes
1answer
26 views
Selecting multiple partial field contents using ArcGIS Select By Attribute?
I can't seem to get the hang of ArcGIS Select By Attributes SQL. I'm trying to "select all that contain Street and Road and etc." Working with polyline SHP.
This works:
"NAME" LIKE ('%Street%')
...
