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

learn more… | top users | synonyms

2
votes
2answers
1k views

How to convert Geometry type column to degree Minutes Second in SQL Server 2008 R2

I have shape column of type geometry and I want to convert X and Y coordinates into Degree Minutes Second format using SQL query and i am using sql server 2008 R2. Currently I got Lat and long using ...
3
votes
0answers
93 views

Preserve datatypes when using SQLite and QGIS

Following Nathan's SpatiaLite/DBManager solution in this answer, I find that once the layer is loaded from DB Manager into QGIS (2.6.1), any calculated fields are appearing as text values instead of ...
0
votes
1answer
68 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', ...
1
vote
0answers
21 views

SpatiaLite / QspatiaLite: get subgroup row ids

I'm using QspatiaLite plugin to join polygon ids to points that are within those polygons. Now I' d like to get subgroup rownumbers for each subgroup of points with same polygon id! Like so.. grp_ID ...
1
vote
5answers
304 views

Selectively removing whitespace from a string with SQL

I have two columns of adresses that I need to join. Column 1 has the adresses formatted as "Some Street 12 A" while column 2 has them formatted as "Some Street 12A." Ie. the last whitespace between ...
4
votes
3answers
411 views

Maximum length of WHERE clause with IN statement

I need to get all features from feature class where their field value matches to input array. To achive this one may set IQueryFilter.WhereClause property similar to 'FIELD_NAME IN (array[0], ...
2
votes
3answers
2k views

Trigger on SQL Server 2008 ArcSDE 10 and ArcGIS Server 10

I need to build a sql server db trigger to update a date field with the current date when a record is inserted or updated, I think. I will be using the date field as a way to recognize ...
1
vote
1answer
865 views

Trigger a python script to run after a row is added to the feature class

I have a Python script which runs on a SDE feature class daily (as a scheduled task). I would actually like this script to run whenever a new row is added to the feature class (data is captured in the ...
3
votes
1answer
277 views

Triggers for delta tables

I have been facing an issue to capture the delta updates on SDE. We have written triggers on A and D tables to capture the delta updates(to capture the version name based on state id's), however I ...
0
votes
1answer
35 views

Modeling SQL Query to add statistical data to a geometry

Hello I have a problem modelling a SQL Query for the following problem: I have two tables with geometries from Shapefile der Verwaltungsgrenzen (WGS84). Of interest for me are Kreise and Gemeinden, ...
0
votes
0answers
51 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
34 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 ...
6
votes
1answer
185 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
78 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
58 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 ...
1
vote
2answers
117 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
61 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 ...
3
votes
1answer
811 views

Cartodb - SQL query of public visualization of a private table

The team at CartoDB have recently(last few days) made some useful updates that now allow public visualisation maps to reference data in private tables. The change has only been made in the last day ...
3
votes
1answer
286 views

Python SQL connection causes crash in ArcMap

I have the following code which I have striped down to just included the part that is causing a crash in ArcMap. import pymssql # Connect to the Research db using Windows Integrated Authentication. ...
0
votes
2answers
70 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
286 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?
0
votes
1answer
62 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
23 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 ...
5
votes
2answers
103 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 ...
1
vote
1answer
827 views

How do I insert a geometry as WKT to SQL Server Spatial with PHP?

I have a web mapping application built on Bootleaf (Leaflet + Bootstrap). Using the leaflet Draw plugin the user can create a polygon on the map. I'd like to insert that polygon into a geometry ...
0
votes
0answers
107 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 ...
0
votes
0answers
47 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
1answer
847 views

Labeling classes for small roads on large extent maps in ArcMap with SQL statement?

I have many small sub divisions that won't label nice, (lot of overlapping) unreadable. While using the bullet leader symbol. What would be the best statement that would place the labels in a ...
1
vote
2answers
86 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
79 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
329 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
73 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
75 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
151 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 ...
2
votes
2answers
220 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, ...
3
votes
1answer
557 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
102 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
93 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
40 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 ...
1
vote
2answers
190 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 ...
3
votes
1answer
137 views

Showing Linestring from SQL statement with CartoDB

I have cartodb sql statement to return what polygons are intersected by a linestring, but I'd like to have the linestring visualized over the polygons on the results. Also not add the linestring to ...
1
vote
1answer
91 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 ...
2
votes
1answer
51 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 ...
0
votes
1answer
96 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
95 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
1answer
33 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
60 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
88 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
143 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
484 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 ...