Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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%') ...