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
1
vote
1answer
64 views

Selecting polygon feature classes with one record in Oracle geodatabase using SQL query?

I have an Oracle Enterprise geodatabase with many feature classes. I would like a SQL query to find all the polygon feature classes with only one record in its table. So far, I can only query all the ...
9
votes
1answer
376 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
37 views

Stop SQL Server selecting outside buffer

I am trying to select all the parks within 200m or a railway. I can easily create and visualise the 200m buffer. However when i try and find only the parks that intersect it i get all parks returned,...
3
votes
1answer
9k views

Using INSERT in PostgreSQL

I am trying to execute the following: INSERT INTO lithic (cp_east, cp_north, type, material, art_count, comment, field_no, gps_unit, initials, rec_date, cortex, geom) SELECT (cp_east, cp_north, ...
0
votes
1answer
46 views

SQL wildcard for Field Name?

I'm wondering if there's anyway to use a wildcard character in a field name. Like using ["Species_%" = 'Hemlock'] instead of ["Species_1" = 'Hemlock' OR "Species_2" = 'Hemlock' ...]. I tried the way ...
3
votes
1answer
540 views

Exporting table with ogr2ogr

I am importing some layers in postgis. Now I will do a SQL-query and the result should be exported as a MapInfo-file: SET PGCLIENTENCODING=LATIN1 set MAPSCHEMA=public set PSQLPATH="C:\Program Files\...
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 ...
1
vote
1answer
43 views

How to merge 2 huge tables with only geometry in common without duplicates

I've got 2 tables with several millions of rows. Because of poor data management, they don't have any ids at all or anything that helps match them up except for the geom I've tried doing insert into ...
0
votes
0answers
35 views

Export a layer from QGIS to Oracle DB

I have a working connection to an Oracle database in QGIS (3.4). I am able to import and edit existing tables into my QGIS project out of the db. Unfortunately it is not possible to export any QGIS ...
2
votes
1answer
52 views

How to get the intermediate points between a route origin and destination?

I use Redshift and recently Amazon Redshift has updated the database to be able to use some spatial functions. https://docs.aws.amazon.com/redshift/latest/dg/geospatial-functions.html In a table I ...
1
vote
0answers
45 views

ArcMap - Definition query with subquery using a date returns wrong result

In ArcMap 10.6, I am using the following definition query on a featureClass to get the features that are underway but the results is not what I expected. Data are stored in an Enterprise geodatabase ...
1
vote
1answer
284 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
1answer
18 views

How to create updateable/inseartable view on QGIS with a sequence id?

I have a huge point dataset that takes a long time to insert new stuff when using QGIS This table has an id column of type serial To go around this problem I've created an empty view that has the ...
0
votes
1answer
27 views

Make Query Layer only returns first row of each table

I have been trying to conduct an arcpy.MakeQueryLayer_management and it is only returning the first two values from each table in the SELECT. When I run the same SELECT statement in Oracle SQL ...
2
votes
3answers
71 views

How to convert UK grid to coordinates in PostgreSQL?

I have a very large file with UK grid references that I need to get coordinates from I'm on a Mac so I cant seem to use the solution provided in this link https://digimap.edina.ac.uk/webhelp/os/...
0
votes
0answers
26 views

Getting SQL explain-plan for ArcGIS Map service

My organization has an ArcGIS map service that contains registered views from an Oracle 18c/10.7.1 geodatabse. I'm having some performance issues with the layers/views in the map service. I want to ...
0
votes
1answer
27 views

Select evenly distirbuted points of unevenly distributed set

I have a task which looks pretty nontrivial yet pretty standard. Let's say, I have a set of points, unevenly distributed on the map (i.e. in some regions there are a lot of points, while in the ...
5
votes
1answer
534 views

Updating attribute values of points outside area using PostGIS

I have two datasets in PostGIS 1.5 (on Postgres 9.1) one of polygons and one of points. I am hoping to create a SQL command that will select all of the points in a dataset that are not within of the ...
1
vote
0answers
45 views

Using QGIS field calculator expression on Execute SQL

The following expression copy only minimum value from group/subset to a new column field in QGIS. Case when "FID_Net"=minimum("class", "FID_Net") Then "FID_Net" Else 0 End But this expression on ...
8
votes
4answers
40k views

Using String Functions (LEFT, RIGHT, MID) in Select By Attributes against a File Geodatabase using ArcMap 10

I finally (albeit reluctantly) made the switch from a personal geodatabase (pGDB) to a file geodatabse (fGDB) not too long ago. The main reason it took so long to switch has to do with how Select by ...
3
votes
2answers
85 views

Combining ST Buffer and Intersect with Where clause

I'm trying to create a buffer around a single point in a spatial entity and finding all the points from a different entity that fall within that buffer. I tried two approaches. The code below runs ...
1
vote
1answer
30 views

QGIS - Execute SQL Failure

I've written an SQL query which works in the query section of the QGIS DB Manager. SELECT Max(S_order), SegmentID, S_Order, Length, Buff_Segme, Buff_Order, Buff_Lengt, Buff_Uniqu FROM "...
1
vote
1answer
38 views

Selecting elements by grouping with one attribute and specific rule using QGIS

I have a question about a specific query for a Shapefile-Layer in QGIS 3.4. I have a Point-Shapefile with the name point.shp. The Shapefile has a lot of different points with a large amount of ...
4
votes
2answers
104 views

PostGIS doesn't automatically convert WKB to GeoJSON

I discovered that somewhere in the upgrades from PostgreSQL 9.5 and PostGIS 2.2 to PostgreSQL 12.1 and PostGIS 3.0 the behavior for transforming a row into JSON changed. I am using the following ...
2
votes
1answer
54 views

Perform intersection using a TRIGGER

I've two geometry table inside my DB: buffer: id INTEGER SERIAL PRIMARY KEY, geom POLYGON polygons: geom MULTIPOLYGON I need have the intersection between buffer and polygons and I will do this ...
0
votes
1answer
24 views

Using features of CARTO Builder to dynamically calculate the average of selected columns

I am trying to find a simple way to calculate the average of selected columns (avg per row) dynamically in CARTO, using only the pre implemented features of CARTO Builder so that it is as user-...
1
vote
1answer
1k views

Selecting elements of specific geometry type in PostGIS

I was reading a postgis tutorial and noticed that unlike working with shapefiles, one can actually store different types of geometry in postgres. In a table created with multiple geometry types, the ...
0
votes
3answers
40 views

Is it possible to dynamically construct the SQL query where clause in ArcGIS layer based on the URL parameters?

I have an ArcGIS layer published to my local server at: https://baseURL/myMapServer/0 I want to dynamically construct the query where clause for this layer based on the URL. For example: https://...
1
vote
1answer
32 views

Length of a street-Spatial SQL

I have a table with the geometry of streets and I`d like to find out, which is the longest and which length (in kilometers) it is. How can I summarize the individual street segments?
0
votes
1answer
52 views

Using SQL query in QGIS Database Manager?

I have below query which I am supposed to import into QGIS by amending certain aspects, to find out exactly what it does. But when I try to run it on the corresponding PostGIS layer, DB Manager warns ...
1
vote
1answer
52 views

QGIS - SQL GROUP BY and MAX

I have a layer which contains a unique ID (UID) for each feature and another column of Scores. For example: UID, Score A12,1 A13,1 A14,4 A12,2 A13,3 A14,5 I would like to use an SQL statement to ...
0
votes
0answers
35 views

Sql query with ogr2ogr for clipping polygons

I'm trying to build a query with ogr2ogr to clip intersecting polygons. I know how to do this with QGIS database manager sql but I can't get it to work with ogr2ogr. With information I've gathered ...
1
vote
1answer
22 views

Cleaning SQL queries [closed]

I have a bit of SQL code that look similar to this: SELECT sum(case when latitude = '0' then 1 else 0 end) as count_zero, sum(case when latitude is NULL then 1 else 0 end) as count_null, sum((case ...
0
votes
2answers
62 views

Isolate the name of the city from an address

I have a data set in CSV format that includes the name of the store, full address, latitude and longitude (from Geocode). Since address is in full format, is there a way I can get a special column ...
1
vote
1answer
45 views

How to filter date values using OGR 2.4.0?

I am trying to replicate a working Oracle SQL query into ogr2ogr, but having issues with a date field where clause. My query is: select BLOCK_STATUS_DATE, GEOMETRY from WHSE_FOREST_TENURE....
2
votes
1answer
30 views

Creating a table view using SQL?

I'm trying to create a table view using SQL to join a table and a feature class together. When I try to create the view, I get an error saying there is an incorrect syntax near the keyword 'CREATE'. I'...
2
votes
1answer
99 views

Get midpoint of SDE.ST_GEOMETRY polylines?

I have a polyline SDE.ST_GEOMETRY feature class in an Oracle 12c geodatabase (10.7.1). I want to create a view on the feature class that has X and Y columns that contain the coordinates of the ...
3
votes
2answers
42 views

ogr2ogr selecting features by location

I'm trying select features from one shapefile that contains features from another shapefile. However when I run the command I get an error " no such column geometry" Both shapefiles are working ...
4
votes
1answer
112 views

ogr2ogr commands with cmd

I'm trying to run a ogr2ogr command with cmd. When I run the cmd the Osgeow command prompt just flashes and nothing happens. When I enter my ogr2ogr script directly to the Command Prompt everythinh ...
0
votes
1answer
202 views

Copying raster to SQLite using GDAL?

I want to copy a raster file into SQLite database. Previously, I did the same with vector data like this: def store_vector_output(self, file_name, identifier): from osgeo import ogr drv = ogr....
1
vote
0answers
23 views

ArcGIS -ArcMap Advanced SQL Expression Returning Blank Output

Want to return the comments that only show "Pending Annexation - Pending City Limits", but is returning a blank value.
2
votes
2answers
2k views

What is faster for geoprocessing (intersect) big shapefiles?

I have 200 Mb shapefiles of polylines downloaded from OSM. I've been working with QGIS 2.0 in Ubuntu 64 Bit 8 Gb RAM to make some geoprocessing tasks and it's taking 2 days for processing. Once I've ...
1
vote
1answer
77 views

Aggregating taxi data to census block data based on number of taxi trips?

I am doing a study on taxi demand using block level census data and taxi drop off and pick data in the form of Latitude and Longitude around the united states. I am hoping to create a map that shows ...
0
votes
1answer
39 views

QGIS Select by Expression Parser Errors

My goal is to select all of the features in a layer with certain names. I have a list of 86 Subdivision names and a I am using a county Subdivision dataset, the names in the dataset include the ...
0
votes
0answers
21 views

ogr2ogr MSSQLSpatial ERROR 1: Got Multipolygon geometry as polygonMember instead of MultiPolygon

I'm very new to using ogr2ogr so I hope I've provided enough detail to my problem. It's worked in the past. I'm trying to retrieve GIS data from a WFS feed and saving it into SQL using the following ...
0
votes
0answers
17 views

Select list of tables and feature classes that have subtypes?

I have tables and feature classes in an Oracle 12c geodatabase (10.7.1). I would like to generate a list of tables and feature classes that have subtypes defined in them. Is there a way to do this ...
3
votes
3answers
2k views

List of all fields and their domains, for each table using SQL (table_name, field_name, domain_name)

I'm trying to list all fields (in all tables in an Oracle geodatabase) and the cv domains that they use. +------------+------------+-------------+ | table_name | field_name | domain_name | +----------...
2
votes
2answers
485 views

Extracting GeometryCollections From SQL

I am trying to convert SQL Shapes to ESRI Shapefiles using the instructions in this blog post from a few years back and OSGeo4W. https://alastaira.wordpress.com/2011/09/29/exporting-spatial-data-from-...
2
votes
1answer
36 views

Updating GeoPackage records with R

I'd like to update records of a GeoPackage containing vector information in R. I've tried this: library(sf) library(RSQLite) nc <- st_read(system.file("shape/nc.shp", package="sf")) # Write Geo ...
0
votes
1answer
165 views

Circular Linestring: Make a new start and endpoint alonge the line (with normalized distance) via PostGIS

I want swap/change the order of the coordinates inside a circular string which should be easy as it is a circle -- circular linestring -- CIRCULARSTRING(5 2,-3 1.999999, -2 1, -4 2, 5 2) to something ...