All Questions
2,007 questions
4
votes
2
answers
492
views
Add newly created columns to an existing PostGIS table from GeoPandas?
I am trying to add some newly created columns to an existing table in PostGIS table from GeoPandas.
Previously, I've pulled the data out of my database, run geocoding, and then created new columns by ...
- 3,908
0
votes
0
answers
72
views
SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended"
I was trying to update one table based on the spatial join table. When I try to run following code:
UPDATE p
SET
p.mileage = ip.length_unusually
FROM
PIPELINE p
JOIN
...
0
votes
0
answers
78
views
Creating non-overlapping boundary polygons with point attributes
I have a set of points that represent the meter locations for my electric company. These points contain a 3 digit code that represents the route number for the meter reader routes. I also have a ...
2
votes
1
answer
117
views
Create a raw SQL statement ready to be used in PostGIS from a geometry in QGIS
Given an existing geometry in QGIS 3.28+ (e.g. from a temporary scratch layer), is there a quick way (i.e. with the lowest amount of user actions) to generate the PostGIS geometry definition (raw SQL ...
- 10.9k
2
votes
1
answer
58
views
Compare field attributes
I am using ArcMap 10.8.2. I have two point layers from different sources which contain the educational facilities of Catalonia. Each layer may contain facilities that are not contained in the other ...
- 65.4k
7
votes
2
answers
656
views
SQL functions supported by ogr2ogr
I have a GeoJSON, test.geojson:
{
"type": "FeatureCollection",
"features": [
{ "type": "Feature", "properties": { }, "geometry": { ...
- 2,690
0
votes
1
answer
68
views
How do I set the result of a SELECT query equal to a new column in a GPKG file using ogrinfo?
This thread: How do I add a new column to a GPKG file using ogrinfo? explains how to add a new column to a GPKG file and set all rows in it to a specific constant value. Here, I would like to add a ...
- 2,690
3
votes
1
answer
63
views
Joining two polygon tables based on conditions
I have two tables: table1 and table2, I would like to join polygons from table1 (purple) to table2 (yellow) if the polygons in table1 don't intersect or are coveredby table 2 polygons. I would like ...
- 30.9k
1
vote
1
answer
3k
views
Convert Latitude/Longitude to Lambert Conformal Conic in SQL
How can I convert coordinates from Latitude/Longitude to Lambert Conic Conformal using SQL?
I've been working on project that will require me to convert between NZTM And LCC projections. It's related ...
- 316
2
votes
2
answers
65
views
How to inverse spatial query [duplicate]
I have two tables with polygons (CTR_daim and CTA_LOW_1111). I have this query to select all polygons from CTR_daim that is intersected by any of the polygons from table CTA_LOW_1111
WITH
...
- 77.9k
4
votes
1
answer
82
views
ST_CLIP with one raster and one polygon
I don't understand how I can select a raster and polygon by ID and then clipping them.
Something like:
SELECT ST_CLIP(selected_raser.rast, 1, selected_polygon.geometry)
From
raster WHERE ID = 99 ...
- 77.9k
4
votes
3
answers
5k
views
Joining two tables using date, time, lat and lon as common field [closed]
I am sorry if the question is elementary but I did not find any clear discussion or guide to understand how to run a query in postgresql. So that a pointer to another website would be also useful. ...
- 78k
0
votes
0
answers
87
views
How do I create a Geodataframe which the data is grouped by User ID & the timestamp is sorted from oldest to most recent?
I've been struggling to complete this exercise (Exercise 2 Problem 3) which you guys can refer to this link. A reminder that the kruger_points.shp is obtained from Exercise 2 Problem 2. Here's the ...
- 77.9k
0
votes
0
answers
275
views
SQL access to GEOPackage data
I am new to the world of QGIS. I work for a company that records birds in order to prepare expert reports. I am actually a programmer and would like to make my life a little easier when analysing the ...
- 65.4k
0
votes
0
answers
20
views
Access control point vertices using SQL
ArcGIS Pro 3.2.1; Oracle 18c 10.7.1 EGDB; SDE.ST_GEOMETRY
Regarding control points:
Video: Suppress symbol effect in ArcGIS Pro
Introduction to control points
CIMGeometricEffectAddControlPoints Class
...
- 1,282
3
votes
1
answer
160
views
How to group/clean group of scattered points based on their distance and attributes?
I downloaded traffic signs from Mapillary API. I found that many of these points represent the same real life entity, but they are scattered in the map. Since they are distinguished by their category ...
- 77.9k
3
votes
2
answers
291
views
File Geodatabase SQL expression to get greatest 1 per group
I have a RoadInsp FC in a FGDB in ArcGIS Pro 2.6.8.
Using an SQL expression in the Select by Attributes tool, I want to select the greatest 1 per group.
In other words, for a given ASSET_ID, I want ...
- 1,282
60
votes
6
answers
42k
views
SQL query to have a complete geojson feature from PostGIS?
I would like to get a geojson feature with properties from PostGIS. I have found an example to have a feature collection but I can't make it works for just a feature.
SELECT row_to_json(fc)
FROM ( ...
0
votes
1
answer
665
views
arcpy.SelectLayerByAttribute_management not selecting anything
I need to run a process on selected records only in a feature class stored in Enterprise geodatabase (SQL Server) that meet a certain condition:
WC = "Status = 'U' AND IN_City = 'Big City' "
...
CommunityBot
- 1
4
votes
1
answer
414
views
Which function should I use to find polygon overlaps with other polygon?
I need a spatial function to detect given polygon is overlapping with existing polygons in the database.
I tried to use ST_Intersects(). It returns true if polygons touch each other. But In my case ...
- 30.9k
1
vote
1
answer
90
views
Point shapefile has duplicate labels in QGIS
I have a point shapefile with a attribute table which has many rows. In the 'Location_Names' column there are occasional duplicates since some people live in the same location. Consequently, there are ...
- 75k
3
votes
0
answers
92
views
Are there ways to speed up building large topology datasets in PostgreSQL/PostGIS?
I used this stored procedure to create a topology for all Census block groups in the contiguous US. It took almost 136 hours to run (on a fast, dedicated workstation - Ubuntu 23.04, PostgreSQL 16, 128 ...
- 187
3
votes
2
answers
157
views
Why following ST_Intersects SQL returns false
I run following code in PostGIS. Both SRID is default, which is 4326.
select ST_Intersects('POINT(104.042455 30.657381)'::GEOGRAPHY(POINT), ST_MakeEnvelope(80, 30, 140, 40, 4326)::GEOGRAPHY(POLYGON))
...
- 43.3k
0
votes
0
answers
54
views
SQL loops and query
I need to apply an ST_distance query to find distance from the given table. How should I tell my query to take points one after the other consecutively on its own or like if the teacher specifies to ...
- 20.3k
1
vote
0
answers
60
views
QGIS - layout/ print composer and linking displayed maps
I wonder if it's possible to display a map in the layout/print composer when another is shown ?
I have this layer where I've made some statistics with diagrams. It shows the percent of people that are ...
- 105
1
vote
2
answers
1k
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 ...
CommunityBot
- 1
1
vote
1
answer
188
views
Buffer/expand point contained inside another polygon
I have a point at one corner of a square. I want to buffer that point with a specified size, but I don't want the buffer to extend beyond the boundary of the polygon behind it. The buffer should ...
0
votes
0
answers
139
views
Edit PostgreSQL database with ArcPy
I have an ArcPy script that works locally with a .gdb database and create some features in this local DB. I would like to migrate my infrastructure on a server and do the same work but on a PostgreSQL ...
- 34.1k
2
votes
2
answers
89
views
Check if polygon falls inside other polygon with 2 layers
I have two polygons layers with SRID 4326, indexed in a PostGIS database. Layer 1 is a country layer. I want to check whether the polygons (> 60k instances) from layer 2, fall inside the boundaries ...
- 43.3k
3
votes
2
answers
252
views
Classifying in QGIS into arbitrary number of percentiles instead of quantiles, based on attribute field value
Extending this question and this one: In QGIS, there are special expression functions for the first and third quartile (q1 and q3) and as well as median (median). So it's easily possible to categorise ...
- 34.1k
7
votes
2
answers
2k
views
Get ArcGIS to recognize ST_POINT
I have a SDE.ST_GEOMETRY line feature class. I've created a view on it that returns an st_point column.
SELECT OBJECTID, SDE.ST_STARTPOINT(SHAPE) AS START_POINT FROM ENG.ROAD
There is a known Oracle ...
- 1,282
0
votes
1
answer
107
views
PostgreSQL and extension tablefunc with enumerated type
In PostgreSQL,
I created an enumerated type
CREATE TYPE w_sebio.t_sebio_type_sad
as ENUM ('transféré', 'traité', 'non soumis', 'ouvert', 'non traité','PAC réalisé');
then a table
CREATE TABLE w_sebio....
- 3,439
0
votes
0
answers
73
views
QGIS how to improve triggers on SQL - SpatiaLite
I'm looking for a way to improve the way my triggers are working on a SpatiaLite layer.
Currently, it takes 45 seconds to work on 1000 entities. Is there a way to improve the performance?
My triggers ...
- 20.3k
1
vote
0
answers
47
views
How to define the points in ST_PointFromText command based on lon lat field
I have a filename with the columns: lon, lat, val1, val2 etc. and I want to join it with a grid shapefile in WGS84. I use the following command but I get an error in ST_PointFromText
ogr2ogr -sql &...
- 289
1
vote
1
answer
125
views
SVG Symbol in Attribute Table
i have few hundred records in table, i need to select them and add SVG symbol to correct one in QGIS.
I created new rule in in attribute table, then i wrote a simple rule (thats only two of much more)
...
- 776
1
vote
0
answers
62
views
Refresh attribute table after SQL trigger
I'm utilizing a trigger on a SpatialLite layer in QGIS.
This trigger is designed to update a field when its content matches with another in the database.
Here's the trigger's code:
CREATE TRIGGER ...
- 825
4
votes
3
answers
1k
views
Changing Number values to specific text in Attribute table (QGIS)
I'm working with a column in an attribute table that has number values and NULL values. I want to create a new column in which NULL values are changed to "Directory" and any number values to "...
- 75k
3
votes
1
answer
105
views
PostGIS fields of type interval not part of QGIS' field list
I have calculated a flight trajectory dataset from open data which has a couple of fields that can be used in GIS (3.32) without any troubles. but there seems to be a problem with fields of type "...
- 20.3k
1
vote
2
answers
170
views
Labeling every polygon in shapefile except for one in ArcMap
I am using ArcMap 10.8.1.
I am trying to create a map of a county which does not need to be labeled since it is for a report concerning that county, but every surrounding county does need a label for ...
- 29.9k
2
votes
0
answers
71
views
How do I find the length of part of a line segment that crosses a polygon boundary?
I have a street centerline table and a borough boundary polygon table. I want to select any centerlines that cross borough boundaries and also return the how much of that centerline is crossing each ...
- 149
2
votes
1
answer
97
views
Representing polygon features using arrows that represent direction of orientation?
I have a polygon layer that stores results obtained from simulations run on a hydrological modelling software, ICM.
A particular table within the attribute table of this polygon layer has details ...
- 10.9k
3
votes
1
answer
110
views
What is the equivalent of native:aggregate() in QGIS Field Calculator?
I have two layers which I want to connect using the aggregate() function with the Field Calculator. I can do this using the processing tool:
processing.run("native:aggregate", {
'INPUT' :...
- 34.1k
1
vote
0
answers
66
views
How to remove fields from insert or updates in QGIS
In QGIS 3.22 using a Microsoft SQL Server database I would like inserts or updates to ignore several columns. The reason being that some columns have values generated by the database. Currently when ...
- 78k
6
votes
3
answers
1k
views
Calculating Convex Hull based on attributes in QGIS
I have a layer with lots of (spatially grouped) lines - lines belonging to a group share the same value in a field.
I would like to create convex hulls around each of these groups using QGIS 3.8.
I ...
- 34.1k
4
votes
0
answers
77
views
In QGIS, can the 'spatialite execute sql' algorithm accept '.sql' text files?
I'm working in Model Designer on a process which includes running an SQL on a SpatiaLite database. I have the connection as the input, but I want the SQL to come from a text file 'xxxx.sql'. ...
- 20.3k
0
votes
1
answer
159
views
pgRouting encapsulated queries error [quotes problem]
I'm facing a quote problem on a pgRouting query.
What I'm thinking is wrong is that my query only works if I use one, then four, then eight quotes on encapsulated queries.
Is there a way to simplify ...
- 3,349
3
votes
2
answers
1k
views
Merge reversed linestring in correct order in postGIS
I want to merge linestring in the correct order in postGIS, but sometime they are reversed so something like this :
select astext(linemerge(ST_union(
ST_GeomFromText('LINESTRING(3 3, 2 2)'),
...
- 3,349
0
votes
1
answer
126
views
Issues with pgRouting workshop code in PostGIS 2.11/pgRouting 2.0
I'm having issues getting the pgRouting workshop code to work on PostgreSQL 9.2/PostGIS 2.11 and pgRouting 2.0 on windows.
How can I rewrite the following PHP/SQL code and make it compatible with my ...
- 3,349
3
votes
1
answer
253
views
QGIS Speed up slow virtual layer
In QGIS I have a virtual layer which does a line generation based on a point shape. The point shape has about 700 point geometries and 26 attribute fields.
SELECT a.Name, a.FromNode, a.ToNode, a....
- 61
2
votes
1
answer
281
views
QGIS Python expression function - returning multiple values for use in a custom SQL query
Custom Python expression functions should return a QVariant-compatible value, which is specified in qgsfunction.py (https://github.com/qgis/QGIS/blob/master/python/core/additions/qgsfunction.py).
So I ...
- 6,417