Acronym for Structured Query Language. A syntax for retrieving and manipulating data from a relational database.

learn more… | top users | synonyms

0
votes
0answers
8 views

Query for finding nearest distance and driving time from objects in a different tables

I have 4 postGIS tables: employees_wgs Facilites and (created by osm2po): at_2po_4pgr at_2po_4pgr_vertices_pgr What I want to do is: make a query to populate two columns in "employees_wgs" ...
0
votes
0answers
17 views

How to save SQL-like WHERE as .qqf in QGIS 2.6?

I used to use QGIS 1.7.4. And there was "advanced search". I can save SQL where clause as .qqf. But I installed QGIS 2.6 and I cannot find this kind of things. Could you please teach me how to save ...
5
votes
1answer
79 views

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

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), ...
1
vote
1answer
57 views

Computing intersections for each geometry in a table

I have a table with many Linestrings and for each of these Linestrings I need to figure out which other Linestrings, from the same table, intersect with it. If I recall correctly this gives me a ...
1
vote
1answer
36 views

OGROGR and SQL: unexpected reserved keyword error?

As I do the following query on naturalearth places.shp : ogr2ogr -f GeoJSON -sql "SELECT * FROM ne_10m_populated_places WHERE ADM0NAME = 'India' ORDER BY POP_MAX DESC LIMIT 1,30" places.tmp.geo.json ...
1
vote
2answers
72 views

OGR2OGR and SQL: selecting 30 biggest places from a country?

I use ogr2org to filter naturalearth places.shp : ogr2ogr -f GeoJSON -where "ADM0NAME = 'India' AND POP_MAX > 500000" places.tmp.geo.json ne_10m_populated_places.shp I would prefer to select the ...
1
vote
1answer
48 views

How to perform a calculation using attributes of groups of features in ArcGIS?

I have a table with information about 2000 aquifers (thickness, area, depth, volume etc.; there are 20 fields approximately). One of these fields is an internal code, i.e. DK_12_456_XXX; the last 3 ...
0
votes
2answers
52 views

SQL filter to show one layer only if another layer is missing

I have two oracle tables that represent buildings in my town. The first table represent buildings that are surveyed and measured accurately but doesn't cover all the parts of the town while the second ...
2
votes
2answers
201 views

Spatial join versus classical join

What are the main differences between a spatial join and a classical database join such as an equi (inner) join?
1
vote
1answer
33 views

SpatiaLite - WITH, view or sub-query?

I am running on Windows 8.1 SpatiaLite_gui 1.8.0, SpatiaLite version 4.2.0 and SQLite 3.8.5. I need to interrogate one single spatial table whith those attributes: ID | val | val_1 | val_2 | Geom ...
1
vote
0answers
11 views

Needing to find parent zip code of a bunch of missing zip codes in the US

We're using MapPoint to export zip code / territory combinations. Unfortunately, the export doesn't include PO Box zip codes which mean that we're missing a lot of zip codes The PO Box zip codes are ...
0
votes
0answers
60 views

ModelBuilder Iterate Row Selection & Variable Expected Fields in Summary Statistics

I have a model using a Iterate Row Selection. The inline variable produced by the iterator is used in the SQL expression of a Table Select tool. Note that this Table Select tool does not use the ...
1
vote
0answers
82 views

ArcMap “One or more layers failed to draw: An invalid SQL statement was used”

I created a model involving the tool Make XY Event Layer because I want to plot latitude and longitude points in CSV file columns. The model runs without errors and appears to have plotted the points ...
5
votes
2answers
74 views

How to draw radius of a circular point buffer using PostGIS

I have generated circular buffers of points in a shapefile. The generated circles and points share a common ID/key, say pt_id. My question is Is it possible to use PostGIS to generate a third layer ...
4
votes
1answer
64 views

SQL-query in arcpy - SELECT WHERE feature between two values

I need to select features between two values in python with arcpy. However, whenever I try to run the code below, it returns with error message "An invalid SQL statement was used". whereclause = ...
0
votes
0answers
35 views

Apply raster contraints on single table with multiple files

I have a raster table (pre-created using CREATE TABLE) containing multiple individual raster files that I append piece by piece to a final table. The table itself is in its own tablespace 'gisdir' and ...
1
vote
2answers
50 views

How to use an IF THEN statement in an sql Select expression?

I need to select the top 3 records from a table, but depending on an inline variable from an iteration, occasionally one of those top 3 records should not be selected and should be replaced by the top ...
0
votes
0answers
18 views

ERROR: operator is not unique: unknown || geometry

EXECUTE 'INSERT INTO Panden_intersect(St_ints,pnd1,pnd1_area,status1,dtm_strt1,pnd2,pnd2_area,status2,dtm_strt2,CtrDist) Values ( ...
1
vote
1answer
67 views

What options are there for geolocation-aware SQL databses?

I need to implement an SQL database that can make queries using geo-location based logic. e.g. select rows within 500m of coordinate x, y What options are available to do this with sqlite, mySQL, ...
0
votes
0answers
30 views

ogr2ogr export to KML from SQLite, when using SELECT for fields Linestrings are lost

I'm using: ogr2ogr -sql "SELECT * FROM 'trails_utm_utf8' ORDER BY name ASC" -f KML trails.kml trails_utm_utf8.sqlite -s_srs EPSG:900913 -t_srs EPSG:4326 to reorder, re-project and export to KML ...
2
votes
0answers
46 views

SQL Query does not work in ModelBuilder (but works elsewhere)

I am having issues getting a simple SQL query to work in model builder. It works fine when I use it with the standard "Select features by attributes tool", but ModelBuilder doesn't find the required ...
0
votes
2answers
49 views

Postgis: Point-in-polygon check + feature attributes inheritance

I have two tables in Postgis 2.1 database: Polygon features with a reach attributive information; Regular point grid; I would like to get a SQL query returning the regular point grid with ...
3
votes
1answer
496 views

What does “foo” stand for in this PostGIS command?

The help page for the PostGIS function ST_PixelAsCentroids has this line of code as a usage example: SELECT x, y, val, ST_AsText(geom) FROM (SELECT (ST_PixelAsCentroids(rast, 1)).* FROM dummy_rast ...
0
votes
1answer
41 views

selecting elements of a specific geometry type in PostgresSQL/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 ...
1
vote
3answers
46 views

ST spatial queries to select multi-level location

I'm trying to write a query in SQL Server using 1 set of points and 2 sets of polygons. The polygons overlap, and I only want to select the points that fall inside of 1 polygon but not the other. I ...
1
vote
1answer
44 views

Loading raster in postgis and link it with an existing table

Relatively new to PostGIS and I am currently exploring the options to make one of my PostgreSQL Tables spatial. Essentially I want to store a raster object in a table that also contains essential ...
1
vote
1answer
28 views

How to create non-duplicate lines between all selected points in Spatialite?

There is a nice answer for the similar question for PostGIS. I tried to adapt it for my case where I need to connect all points from a point layer, but unfortunately I get duplicate geometries: lines ...
2
votes
1answer
30 views

SQL Server equivelent to Arcmap Intersect for polygons?

I have three different sets of GEOMETRY Polygon datasets in SQL Server that completely overlap. I need to create a new dataset that has a polygon for each unique area of the three layers. So ...
2
votes
2answers
88 views

Get Coded Value Domains from a Oracle geodatabase using PLSQL

i'm trying to get all coded value domains from a Oracle geodatabase. I dont have access to arcmap. I only have access to oracle 10g database. SELECT I.NAME Domain, ...
0
votes
1answer
34 views

Get a sql view to persist in a CartoDB visualization

I'm new to CartoDB and so far impressed. But I'm struggling with how to get a sql query to persist in a visualization. I type it in, like 'SELECT * FROM env_feedlots where active = 'Y' and BIRDS > 0', ...
0
votes
1answer
44 views

How to preserve GPX track point (trkpt) info when performing ogr2ogr with sql ST_union operation

I am performing GDAL ogr2org operation with ST_union(Geometry) on tracks from GPX file to split those at intersection points. Track points have elevation and depth info. In GPX track point entry looks ...
2
votes
1answer
59 views

If do not want to use pgRouting then what?

Good evening fellow GISers, I am interested in creating an online routing application. So supposing I have all the data I want into a postGIS database and loaded onto GeoServer and I am willing to ...
0
votes
0answers
116 views

Geometry datatype to latitude /Longitude MS SQL Server

I need to convert Sql Server geometry datatype to latitude /Longitude I have just imported a shapefile to my table (geometry) This query does not work:- select geom.Lat as [Latitude], ...
0
votes
1answer
16 views

create Geoserver SQL Views

I want to create an SQL View in my local Geoserver (2.4.4), acccording to the handbook. http://docs.geoserver.org/stable/en/user/data/database/sqlview.html They say: In order to create a SQL View ...
2
votes
0answers
32 views

SQL Error Message: Find and Replace for Date Field

Hi, I am attempting to use the find and replace function within the Attribute Table Options to replace fields that contain dates in the following format: month/day/year (e.g. 03/14/2014). The ...
2
votes
1answer
41 views

How to populate geography point using ST_MakePoint(long, lat)

I have a table called customer_campaign, and there is an empty column geom(geography) in it. So I want to fill it up by using ST_MakePoint(long, lat) in PostGIS, lat and long are two columns in the ...
0
votes
0answers
60 views

Error selecting geometry from Oracle DB

I'm trying to select a geometry column as well-known text from a feature class in an Oracle 11g/ArcSDE 10.1 geodatabase. (This is in a Python script using pypyodbc as a driver). This is my SQL: ...
9
votes
5answers
310 views

GIS analysis to find duplicate geometries

I have a big shapefile that contains all the buildings and houses of the town that I work in (approx. 90,000 features). The data of the buildings/houses are saved by the town's surveying engineers and ...
0
votes
1answer
78 views

dealing with geometry that should be geography (bad coordinates)

I'm working on a project with a few publicly available datasets. One of them is from the USGS, it's their prediction of peak ground acceleration in 50 years. Here's a link, and here's what it looks ...
3
votes
2answers
103 views

How can I extract all distinct values from a PostGIS Raster?

I am working with an integer raster in PostGIS 2.1 and I would like to extract all distinct values in that raster. I was hoping to be able to do something along the lines of the following query, but I ...
1
vote
0answers
35 views

Tables from SQL Server to QGIS Layers

I'm a beginner SQL and GIS user and this my first time using stackexchange, so apologies if I'm asking the question properly. I'm trying to open some MSSQL tables in QGIS. I established a connection ...
6
votes
1answer
233 views

How to use ST_DelaunayTriangles to construct a Voronoi diagram?

With PostGIS 2.1+ we can use ST_DelaunayTriangles() to generate a Delaunay triangulation, that is a dual graph of its Voronoi diagram, and, in theory, they have an exact and reversible conversion. ...
0
votes
0answers
8 views

PostGIS-Layer: sql query from style rules

I’m using PostGIS layers which are build from OSM-Data (osm2pgsql). Now I like to build a sql-query to filter the needed objects like "waterway" = 'river'. But in the layer-style I already have that ...
0
votes
0answers
21 views

Adding a column to several feature classes in a file gdb using ogr2ogr

I'm trying to add the same column to 25 feature classes in an ESRI FileGDB. I managed to do this for one feature class by using ogr2ogr: ogrinfo C:\temp\test.gdb -sql "ALTER TABLE table01 ADD COLUMN ...
1
vote
2answers
44 views

How to add the values in two columns to a new column on a sync table?

We have field data coming in from an app platform and we want to drive a CartoDB visualisation to help with planning. A bunch of rangers go out surveying sites to count crocodiles. For each site, the ...
2
votes
2answers
41 views

SQL 2012 code that spatially polyline fine (join file) to a point file (target) and provides Distance Field

I am currently working in ArcMap 10.2.2 but would like the SQL code to create the same output as that provided by the Spatial Join tool. Target feature = point, Join Feature = polyline, Option ...
3
votes
1answer
71 views

PostGIS: Get closest point between geometry and Multipoint

I think my question is not that difficult for GIS professionals, but i didn't found an answer to my problem (even not in other questions). Problem: I have given two tables (table1,table2) with Points ...
1
vote
1answer
115 views

Definition Query to exclude certain values and return remaining values

I have been trying to create a Definition query to symbolize a set of water features. I cannot edit the .shp file and I want to exclude a multiple values. I have tried using NOT but have had no luck. ...
2
votes
1answer
50 views

Perform a group by query on geodatabase in ArcMap

I have a geodatabase called predictions.gdb and a feature class called output_gis_120_Project in that database. I imported this feature class as a layer in ArcMap. The attribute table with columns ...
2
votes
1answer
67 views

Model Builder Query by date

I've created a model that does several spatial joins based off of a lat/long in a table. In the table, I have a "Modified_date" which gives year, month, date, time. This model is set to run ...