A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

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
2answers
157 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
408 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
98 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
80 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
111 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
82 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 ...
2
votes
2answers
447 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 ...
11
votes
2answers
283 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 ...
4
votes
1answer
94 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
88 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
1k 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
56 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
152 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
112 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
291 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
1k 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 ...
5
votes
1answer
91 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
783 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
281 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 ...
2
votes
2answers
64 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
174 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
989 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
86 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%') ...
4
votes
1answer
213 views

Returning US Census geographies within range of coordinate using PostGIS and Tiger Decoder?

TL;DR: How do I write a query in SQL (postgres, postGIS, tiger extension) that will return census geographies (blocks, tracts, zcta) within an X mile radius of a lat/lng coordinate? Background: I ...
0
votes
1answer
99 views

Using st_union for joining overlapped areas in PostGIS?

I have two land use maps for London which have an overlapped area. Can I use the st_union function in postgis to join them together? The attribute table contains information includes legends and ...
1
vote
1answer
150 views

GPSQLExpression - python toolbox

I am relatively new to python and I am trying to build a tool within python toolbox with GPSQLExpression as parameter. I am having trouble with populating its field and value list. Below is part of ...
1
vote
1answer
73 views

pgr_alphashape returns a self intersecting polygon

After using the function pgr_drivingdistance() I am attempting to make a polygon with pgr_alphashape. Following the example found in the documentation I should do the following: https://docs.pgrouting....
1
vote
1answer
394 views

Select by attribute how to only select numbers in a string field

Using a SQL experssion in the "Select by Attributes" table, how would I only select records 1, 4, 5, 8 and omit the rest (I only want to select values that are entirely numbers)? What I have right now ...
1
vote
2answers
317 views

Save query result from QGIS to csv

I have a layer saved to spatialite which I want to query and save part of to csv. Even the query result takes a very long time to load to the browser (2.5M rows) even if I load it without geometry. ...
1
vote
1answer
43 views

Sorting by shape token in ArcPy cursors?

I have a script that uses the sql orderby parameter to sort my arcpy cursors by a user defined field or fields and deletes duplicates, but keeps the duplicate with the highest value. I'd like to be ...
2
votes
1answer
104 views

Is it possible to bring PostGIS database Query results into QGIS workspace other than using DB Manager?

I would like to use the QGIS modeller (or similar) to import SQL query results form my Database directly into my workspace. I am asking as part of my job is to repetitively add vector and DTM layers ...
2
votes
1answer
153 views

Using R and SQL together in sf - what is an efficient workflow?

Do anyone have any tips/tricks/workflows for working with an SQL query within a read_sf/st_read function call in RStudio? (I am struggling to figure out how to make a postgrsql/postgis example ...
1
vote
1answer
296 views

Transfer data from SQL database to postgre database using GDAL

I am new to GDAL and have been uploading CSV crime data to a postgis database using ogr2ogr. ogr2ogr -f "PostgreSQL" -lco GEOMETRY_NAME=geometry -lco FID=gid PG:"host=server user=user dbname=dbname ...
1
vote
1answer
109 views

Return rows after a spatial join where the count is zero

I am trying to determine the number points in a table of polygons AND include a row of zero for those instances where a polygon has no points in it. This query works well except that no zero rows are ...
0
votes
0answers
35 views

Microsoft SQL Server - Multi-user solution

My team and I are currently trying to apply multi-user techniques in our projects. So far we have used an ArcGIS Enterprise build for this purpose but we quickly learned there are a few limitations. (...
10
votes
1answer
357 views

How do I select the longest connected lines from PostGIS ST_ApproximateMedialAxis?

I used PostGIS ST_ApproximateMedialAxis to transform polygons representing mountain ranges into a series of lines. Now, I'd like to select only the "longest" line segment for each group, so that I ...
0
votes
1answer
353 views

Why should I use PostGIS functions? [duplicate]

Is there a reason for using PostGIS functions such as ST_Area and ST_Length when there are already functions/operators for doing the same thing in PostgreSQL (area and @-@)
1
vote
1answer
82 views

How to convert SRID's on the fly and via command line, on INSERTION's query in PostGIS?

We are working on a project that used shapefiles in order to build our first PostGIS database, through a tool called “PostGIS Shapefile Import/Export Manager”. The problem is that now we need to ...
1
vote
2answers
368 views

Calculating distance between a Point and a MultiPolygon PostGIS object

I'm working in a layer with MultiPolygons and I'm trying to calculate the shortest distance between a MultiPolygon and a point which is contained in that MultiPolygon, using Django and PostGIS. At ...
2
votes
0answers
55 views

Select events that represent the most recent work on roads

I have a question on Stack Overflow where I ask about Selecting events that represent the most recent work on roads. So far, I have been provided an answer that seems to work, although it is quite ...
0
votes
1answer
94 views

'OR' is not working in SQL statement

I am trying to come up with a SQL statement that will select the data based on multiple criteria and using multiple fields, but it is not returning the correct results back. Example: Depth < 1 OR ...
1
vote
0answers
55 views

ArcGIS PostgreSQL Trigger

I currently have a trigger defined in my database which executes when my edits in ArcMap are saved using the AFTER INSERT ON method. Is there anyway to execute this trigger without having to select ...
1
vote
0answers
651 views

Google Maps API - Multiple Polygons

I'll be generating my JSON file using PHP and SQL for displaying around 200 polygons, on a map. I've managed to test the concept with two polygons but still struggling. I'm not experienced with JS or ...
1
vote
1answer
76 views

Python script in Qgis for hundreds of texts replaces or gigant 'replace'+'case then else' combined expression?

I want to replace a text from one collumn into another text in a different collumn and base the condition on two different input collumn/fields. Collumn 1: Integers (from 1 to 10) Collumn 2: 100 ...
2
votes
3answers
290 views

ArcGIS Desktop 10.3 - definition query to exclude integers starting with certain values

I have an integer field in a polygon shapefile that I want to definition query out everything number that beings with 18. These numbers aren't sequential and are of any length, ie I want to exclude ...
0
votes
1answer
70 views

Skip duplicate records when appending using arcpy?

The objects I want to check have unique ID's (edit: these ID's are different from OID's and will remain the same when copied). I'm appending one file geodatabase to another, and I want to make sure I'...
0
votes
2answers
364 views

ERROR 000358 When trying to select by attribute using “IS NULL” query

I am trying to select a layer by attribute (field is null) using a stand alone python script. #Select layer by attribute to find any null values arcpy.AddFieldDelimiters(mayIntLyr, "...
3
votes
3answers
147 views

SQL Query Syntax gives ERROR 000358: Invalid expression?

I'm writing a script that will cycle through a shapefile of building footprints for an entire state, create a folder for the individual counties in that state, and then intersect the building ...
0
votes
3answers
780 views

Querying Geospatial data in Athena database, getting rows in range of 5 miles from specified coordinates

I have database table Building with these columns: name, lat, lng How can I get all Buildings in range of 5 miles from specified coordinates, for example these: -84.38653999999998 33.72024 ? ...
0
votes
1answer
38 views

Subset a shapefile that excludes certain field values by using NOT in Select by Attribute where clause

I am using ArcPy 2.7 to subset a shapefile. I am trying to use arcpy.Select_analysis to exclude certain features from a shapefile (idle fields denoted as 1401 in the field 'Crop_Type'. my 'Crop_Type' ...