We’re rewarding the question askers & reputations are being recalculated! Read more.

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
0
votes
0answers
87 views

Selecting bounded area within raster [on hold]

I am new to PostgreSQL. I have imported a 256kb sample raster (.tiff) into postgres with tilesize 100x100. Now I need to select the area (rectangle) from the center of the image using sql query. Then ...
2
votes
0answers
20 views

How to load .geojson file w/ properties and polygons into SQL table?

I have an Azure SQL table: -- Create the table in the specified schema CREATE TABLE dbo.geoTest_DEV ( fAssetId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, fAssetGeoId [NVARCHAR] (50), ...
1
vote
1answer
9 views

Extract subtype codes and descriptions in a view

I have an Oracle 10.7.1 geodatabase that has tables and feature classes with subtypes. How can I extract the codes and descriptions from the subtypes in a view?
1
vote
0answers
19 views

Selecting by location and attibutes simultaneously? [closed]

If I have a feature class ( say 'Cities') and I would like to select all features from another layer ('Incorporated Places') that nest within the cities layer and also have the same value for their '...
2
votes
1answer
34 views

Why do two tables with valid geometry return self-intersection errors when intersected?

I have two tables A and B, both have valid geometries when I check with ST_IsValid. In my query, I intersect the two and the query stops in the middle because of this error: ERROR: ...
16
votes
3answers
33k views

Converting MultiLinestring to Linestring with PostGIS?

I have a multiline string and want to convert it to a linestring in order to use some functions that only work with linestrings. I used ST_Dump() and got a collection of my geometries. If I merge them ...
1
vote
1answer
29 views

Count subset of points in polygons

Being an SQL-noob, I found a working solution to generate a virtual layer with counts of points in the polygons of another layer, inspired by this https://gis.stackexchange.com/a/54680 SELECT ...
3
votes
1answer
2k views

Selecting top 5 most expensive items in personal geodatabase

I'd like to know what is the best way to select the 5 most expensive items (in terms of actual monetary value) in my personal geodatabase where i have a price field. Prices are not necessarily ordered ...
4
votes
1answer
43 views

Aggregating multiple rasters within a polygon in PostGIS

I have several raster tables in PostGIS (four in fact but in this case let us assume there are just two) - same cell sizes, same alignment but with different types of value (e.g 3d_pot, 3d_area). ...
2
votes
2answers
34 views

QGIS 3.4 feature filter MAX(), GROUP BY does not work

I thought my problem was solved with the question I asked bevor (QGIS 3.4 feature filter maximum() does not work). But there is still a problem. I still want to filter a layer with the maximum value ...
3
votes
1answer
69 views

Limit on Filter Query Stipulations?

I am novice mapper. I ran into some trouble with QGIS today when trying to add additional census tracts to a layer filter. The tract exists within the layer I'm filtering, but there seems to be a ...
1
vote
1answer
25 views

Adding Encryption options to GeoServer SQL Server connection

I have set up an HTTPS connection between GeoServer and an Azure database using the SQL Server extension, however I would like to add encryption options to the connection string. Is this possible in ...
3
votes
2answers
93 views

Calculating azimuth values relatively along line for its segments using PostGIS

I'm trying to make this calculation in SQL with PostGIS: azimuth_1 + (Sum rel_azimuth_2 to rel_azimuth_7) rel_azimuth_x is always the angle beetween the actual subsegment_x and one subsegment_x-1 ...
1
vote
1answer
394 views

SQL in CARTO - round a function with two decimals in carto

I am try to round the expression below on carto but it still returns no decimal places. The columns are 'number' type. I have also tried SELECT round( CAST(column as numeric), 2 ) suggested here but ...
7
votes
1answer
111 views

Spatialite spatial queries do not function on GeoPackage

I have downloaded some data (as GeoPackages) from this excellent website: https://data.mfe.govt.nz/layer/52757-petroleum-wells/ and https://data.mfe.govt.nz/layer/52675-location-and-extent-of-nzs-...
1
vote
2answers
69 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
43 views

PostGIS create table with multiple geometry

I want to create a table with a geometry field in it. e.g.: CREATE TABLE ptgeogwgs(gid serial PRIMARY KEY, geog geography(POINT) ); In my case, this geometry field can either be a POINT, a ...
1
vote
0answers
19 views

Improve OGR SQL query for polygon intersection using WKT geometry

I want calculate the intersected area between a shapefile and WKT geometry. My target big layer: a.shp My query small layer: 'POLYGON((1150046 1264030, 1160100 1263793, 1160079 1260606, 1150218 ...
1
vote
1answer
51 views

Extract intersecting polygon from multipolygon search

I'm using the latest SQL Server and trying to build a query where i can search a database of spatial data and find the intersecting polygon. The problem is that the spatial data can be of ...
1
vote
1answer
45 views

Error while using ST_Line_Interpolate_Point: first argument isn't line

I've been running a scheduled task that contains some SQL code that has been running fine up until a few weeks ago. I don't believe any of the code had changed recently so I went through the debugger ...
0
votes
0answers
64 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
1answer
55 views

How can you convert between geographic data from and to 3D data in PostGIS?

Is there was a way to convert a geography value to a 3D geometry value (in ECEF, for example), and vice versa? I know how to convert between geography using a 2D projection, but not a 3D reference ...
2
votes
0answers
346 views

Oracle Spatial: Error with SDO_UNION

I have a table which contains address-points. With following sql-statement i have created a 100-meter buffer around each address-point: CREATE TABLE BUFFERPOLYGONS AS SELECT a.OBJECTID, SDO_GEOM....
0
votes
0answers
51 views

Spatial intersection of features (Oracle Spatial) with a line stored in a local gml file?

Interesting sites are stored as polygon features are stored in an Oracle Spatial read only dB. I have a local GML file containing a line (EPSG:31370 - corresponding to a trajectory over a couple of ...
1
vote
2answers
77 views

Why do arcpy.GetCount_management and arcpy.ArcSDESQLExecute yield different row counts?

I was benchmarking which method was faster for counting the number of rows in a table, when something curious happened: import arcpy import os import time test_tuple = ("path\to\connection", "...
0
votes
0answers
25 views

ST_TRANSFORM SDE Oracle does not convert the geometry

I try to convert a geometry from 31370 to 3857. But it does not work. The coordinates stay in 31370. DB: Oracle 11 ArcSDE: 10.4.1 The code: select sde.st_srid(shape) ,sde.st_x(shape) as ...
1
vote
1answer
144 views

How to remove artifacts with SQL Server GEOMETRTY::UnionAggregate()

We'd like to do an effective "real-time dissolve" on an Esri feature class with a SQL GEOMETRY shape where a parcel layer is "dissolved" into products using an attribute (ProductID) of the parcel ...
2
votes
1answer
45 views

PostgreSQL create line from points

How do i create a linestring from points sort by date in PostgreSQL? I tried: SELECT observations.id, ST_MakeLine(observations.geom ORDER BY observations.date) AS newgeom FROM observations ...
1
vote
0answers
22 views

PostgreSQL points to line error [duplicate]

I have GPS locations with date. These are points. I want to convert the points to lines, sorted by date. Using PostgreSQL Tried: SELECT observations.id, ST_MakeLine(observations.geom ORDER ...
0
votes
0answers
19 views

Hiding one field of geodatabase for some users

I have ArcGIS Server 10.3 and SQL Server 2008. I have a geodatabase which is connected to SQL Server by using ArcSDE. My users connect to the database by IP address and Username and password. I want ...
7
votes
1answer
1k views

SQL “Case” statement when count(*) is 0 [closed]

I am trying to write the following SQL command using case clause. with x as (select the_geom as geom from table1 where st_dwithin(the_geom, st_setsrid(st_makepoint(28.959495,41.019913), 4326), 0....
5
votes
1answer
87 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 ...
1
vote
1answer
19 views

pivot-table and group geometries

Postgresql11 - postgis2.4 Here is a table mytbl with points and an attribute var : geom | var pt a pt b pt1 a pt1 a pt1 b pt2 c ... I want to "rotate" the table like this : ...
4
votes
1answer
67 views

Joining points using PostGIS?

I have a set of points which represents a series of telegraph poles. I want to link them to simulate the wire running along them. The code I've been using is shown below. SELECT DISTINCT ON (p1.id)...
33
votes
5answers
19k 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 ( ...
3
votes
2answers
65 views

Distance between points inside LineString

I have a collection of geometries represented by a linestring. Each has at least two coordinates. Is it possible to write a SQL that instead of returning coordinates, return a distance between them. ...
1
vote
0answers
28 views

Use selected features to generate WHERE clause in ArcGIS for Portal

I have a web map in Portal that has a point layer called Work Orders. I believe it's possible to manually select points in the layer by using the Web AppBuilder Select Widget. Using selected points, ...
3
votes
1answer
141 views

How do I use pre-existing Lat/Long data in a SQLite table with QGIS?

I have an application that stores its data in a SQLite database. I can't modify the application or database to add geometry extensions, but it does already have latitude and longitude columns for each ...
1
vote
1answer
173 views

Wrong results joining places to create flow map

I have to do a flow map (home->working place) for my area of interest (28 french cities). I get a database .dbf created by a french institute. I imported it into postgres/postgis. One row = 1 people. ...
1
vote
1answer
104 views

Adding geodatabase connection from another PC to SQL server 2014?

I have a problem that I can not solve. I install SQl server 2014 and create Test1 database on My PC. Now I want to add Geodatabase connection in Arccatalog on Another PC. But it error below. Firewall ...
0
votes
1answer
23 views

ArcMap SQL Trim Def query code

How can I get this ArcMap SQL Trim Def query code correct? I have a attribute field that looks like: LOC: R1, THEN A BIG LONG BUNCH OF WORDS or LOC: L10, THEN A BIG LONG BUNCH OF WORDS What I need ...
1
vote
1answer
122 views

Read height data from Meridian 2

I'm having difficulties reading the Gridded height data (OS Meridian 2). I'm loading with OSGB 1936 reference system but have trouble finding the lat/lon data for each point. i.e.I open the attribute ...
2
votes
2answers
156 views

Adding field in vector layer from attribute table in QGIS? [duplicate]

I have an Attribute Table named 'data.shp' and a Vector Layer 'pop.shp'. The 'data.shp' has the following fields "ID", "value1", "value2" The 'pop.shp' has the "ID" field (same with the 'data.shp') ...
1
vote
0answers
22 views

Unexpected query layer results: Why does unioning with DUAL fix it?

Simple Query: I have a query that connects to a table in a work order management database (Oracle 12c; non-GDB). select wo.wonum from maximo.workorder wo The query returns 428 records in ...
1
vote
1answer
33 views

Measuring area covered by linestring

I have a trajectory given by GPS which I store as a ST_LineString() and I'd like to know the area covered by the line string. I know the "width" of the trajectory, but I'm not sure how to approach the ...
1
vote
1answer
63 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
55 views

Selecting polygon feature classes with 1 record in ArcSDE using SQL query?

I have an ArcSDE database with many feature classes.I would like SQL query to find all the polygon feature classes with only record in its table. So far, i can only query all the feature classes. ...
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 ...
0
votes
1answer
36 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,...
0
votes
1answer
40 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 ...