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

learn more… | top users | synonyms

3
votes
2answers
351 views

adding geometries to a source, target table for multiple rows in pgrouting

I am having an issue trying to perform the pgr_dijkstra algorithm and getting back a line in my table for multiple rows. I have a table that has a columns of source, target node id's but I cannot ...
3
votes
1answer
103 views

Use selected value in postgis function

How can I use the selected value from a SELECT statement in a PostGIS function. e.g. using the geom_way from SELECT geom_way FROM osm_2po_4pgr ORDER BY ST_Distance(ST_GeomFromText('POINT(21.323329 ...
3
votes
1answer
401 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 ...
3
votes
1answer
690 views

How to merge connected lines with same direction (PostGis)

I am looking for a way to join all lines that are connected, have the same direction, i.e. start point of one is end point of the other, and share some common attributes. In fact, I want to connect ...
3
votes
1answer
572 views

What formula does SQL Server 2008 use in STDistance?

I am analyzing the accuracy of the STDistance function for geographical objects in SQL Server 2008, in order to understand its behaviour better I need to find the underlying formula. Does anyone ...
3
votes
1answer
311 views

Given a lat/long point, determine if it's land or water?

I have a PostGIS-enabled PostgreSQL 9.1 installation with the 10M physical dataset from Natural Earth imported. It's been converted to 900913, and in QGIS I can visualize the data. So the plumbing's ...
3
votes
2answers
196 views

Traversing a PostGIS table tree

I'm developing a geospatial db for a company that operates at a number of large facilities. Its primary purpose is to help staff locate themselves and equipment within each facilities' multi-storey ...
3
votes
1answer
292 views

Editing and Deleting Query Tables in ArcMap

How do I edit the query for a Query Table after it has been created? How do I permanently delete a Query Table? Even after I do right-click -> remove, it still appears in dropdown lists.
3
votes
1answer
334 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. ...
3
votes
1answer
26 views

Change feature class from ESRI native geometry to SQL Server geography

Original question: I have found a lot of documentation on importing and using query layers from SQL Server into ArcCatalog/ArcMap/etc. However, when you change the projection of a feature class or use ...
3
votes
0answers
39 views

Get domain descriptions for multiple fields using SQL (ArcSDE 10.3/Oracle back-end)

How can I get domain descriptions for multiple fields using SQL (ArcSDE 10.3/Oracle back-end)? I have successfully used the Oracle SQL in the ESRI document: Resolving domain codes to description ...
3
votes
0answers
55 views

SQL SERVER 2012, Python subprocess, OGR

Need help with uploading shapefile with srid 102008 to SQL server 2012. I ran the ogr2ogr script in the ogr shell and it uploaded the shapefile within seconds but when I try using python subprocess to ...
3
votes
0answers
46 views

How to use pythons' GeoAlchemy2 to filter on dynamically generated Points?

I'm trying to do something like this SQL query in Geoalchemy2: SELECT district_polygons.district FROM markers, district_polygons WHERE ST_Contains(district_polygons.multi_poly, ...
3
votes
0answers
100 views

Visualizing variability in the size and shape of polygons

I am working with a data set of area restrictions imposed by criminal courts. These restrictions ban a person from entering a demarcated area. These restrictions vary in their size, shape, and ...
3
votes
0answers
152 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 ...
3
votes
0answers
285 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 ...
3
votes
0answers
477 views

How to export data from sql server into shapefile in the decimal format in QGIS 2.4?

The last couple of days, I have been working on exporting data from SQL to a shapefile in QGIS 2.4. While converting into a shapefile, the data type changes automatically from Float to Integer in the ...
3
votes
0answers
110 views

How to express not within/inside predicate in spatial database with good performance

In our application we want to express some spatial predicates as objects not within some areas, but unfortunately it has very bad performance. The following SQL is generated by our application: ...
3
votes
1answer
778 views

How to create a search box and SQL statement for searching an attribute field?

How can I add a HTML search bar that the user can use to search an attribute field for a polygon layer, and then tie that input into the SQL statement? So far I am able to hard code the query of ...
3
votes
0answers
434 views

Geoserver WMST Date Format

There is table in SLQ-Server 2008 R2. In this table i have date column with date format like this 2006-09-08 14:39:41.000. Now i make View from this table on Geoserver side but do nothing wiht date ...
3
votes
1answer
476 views

SQL Server UNion of geography feature returns FULLGLOBE result?

I have some trouble with sql server 2012. I store coordinates of french cities in my database as geography object (with ESRI 4326) and i want to make the UNION of some cities. I have in result ...
2
votes
4answers
2k views

Finding center point of MULTILINESTRING using PostGIS?

How to find the center point of MULTILINESTRING, which lies on the multiline? I am using SQL in PostGIS
2
votes
3answers
2k 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?
2
votes
1answer
158 views

Case sensitive query of shapefile in ArcMap 10

I am an IT guy, not a GIS guy. What I know about how to use ArcMap 10 I learned from Google. It has served me well so far. Apparently what I'm wanting to do worked in version 9, but no longer does in ...
2
votes
2answers
3k views

Formatting SQL queries for use in Arcpy?

I have to make an SQL query on a column having not adequat name "type_borne_id", I couldn't make SQL queries using arcpy.Select_analysis neither selectbyattribute, but when I use select_analysis ...
2
votes
3answers
195 views

Find point close to points from postgis table

I have a postgis point table with a large list of cities. I need to find duplicates in it, but only if they are 10km close each other, which means that is the same city. For example, I have 5 points ...
2
votes
2answers
348 views

Add a serial id column inside a view

Is it possible to create a new id serial column inside a view? I need it to be able to add my view in postgis I have made a full outer join on two tables I need a unique PK to load the view in ...
2
votes
1answer
5k views

Trouble with INSERT in PostgreSQL 9.1

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, ...
2
votes
2answers
1k views

Select by Attributes - If string has a letter

I'm running ArcMap 10.1. I'm using a File Geodatabase. I have a string coordinate field that I want to clean and some records have letters in it (mostly 'N' or 'E' or 'n' or 'e' e.g. '6213210N' or ...
2
votes
1answer
114 views

Selecting more than one feature from raster attribute table using ArcGIS Desktop?

How do I select more than one features from raster attribute table? For example: "lc_2001" = 21, 22, 23, 24 AND "lc_2006" = 11, 31, 41, 51
2
votes
1answer
156 views

How to use ST_DWithin to query features from one table within a distance of a specific feature or features in another table

I'm pretty new with PostGIS. I have a parcel layer and a census block layer in a postgres database, and I would like to be able to query out all census blocks within a specified distance of a specific ...
2
votes
1answer
174 views

Show Log of the Sql statements that QGIS generates to retrieve Data from Postgis

Is there a ways to - make QGis log its generated SQL Statement or to - force POSTGIS to log all interaction with QGIS? There is something strange happening to the WFS layer I retrieve in QGIS, since ...
2
votes
3answers
607 views

How to use STUnion with GroupBy

I'm using SQL Server 2008 R2 to store Geometry data types. I have a bunch of lines as individual features, that share common names ('NAME' field). I would like to create a view that Groups By 'NAME' ...
2
votes
2answers
439 views

Using MAX in SQL Expression of ArcGIS ModelBuilder?

I am trying to use a SQL expression in order to select values that are >= 25% of its max value in that column. For instance, VALUE >= MAX(VALUE)*0.25, and those will ultimately be the selected ...
2
votes
3answers
1k views

SQL expression error for Select Layer By Attribute

arcpy.MakeFeatureLayer_management("N05-12_Station2_JGD.shp","station_12") year=2012 arcpy.SelectLayerByAttribute_management("station_12","#",'"closing">=%d AND "opening"<=%d' % year) ...
2
votes
1answer
3k views

Why isn't my select by attributes query working?

I'm running ArcMap 10.1. I have a point feature class joined to a table and am trying to query a field. If I do [field] = 'TEST', it selects all the fields. But if I do [field] LIKE 'TES%', it ...
2
votes
1answer
1k views

PostgreSQL, find points near points

I'm going to have a database with a 'people' table and a 'shops' table. I'd like to find all people within 500m of a shop, and details from both tables. I'm used to working with mySQL, but I'm ...
2
votes
2answers
245 views

Is there a way to save manual QGIS changes to Postgis to SQL script?

I am manually editing a Postgis layar with QGIS and would like to export these manual changes in the form of an SQL script. This way: I can do it automatically next time i need to it is easier to ...
2
votes
1answer
223 views

Connecting QGIS, SpatiaLite and Python?

I am trying to connect with python code, QGIS and Spatialite, to do SQL query. I have read some tips and I tried with this code, but no add anything. Any Idea? uri=QgsDataSourceURI() ...
2
votes
2answers
68 views

CartoDB's SQL API: Row too large

I am trying to run the following PostGIS query using CartoDB's SQL API: SELECT the_geom, ST_Area(ST_Intersection(the_geom::geography,ST_Buffer(ST_SetSRID(ST_MakePoint(-52.4379, ...
2
votes
2answers
1k views

Determine which points are contained inside a given polygon

I have these database tables defined: CREATE TABLE regions ( id SERIAL PRIMARY KEY, name VARCHAR(150) NOT NULL ) CREATE TABLE region_points ( id SERIAL PRIMARY KEY, region_id SERIAL ...
2
votes
1answer
764 views

Spatial joining syntax

A standard PostGIS point-in-polygon query might look like this. (example tutorial) SELECT p.*,b.polyname FROM points p, shapes s WHERE ST_Within(p.the_geom,s.the_geom) Imagine this was not a ...
2
votes
2answers
472 views

How can I create a hexbin layer using the SQL and CartoCSS parameters in cartodb.js?

CartoDB has a great hexbin visualization wizard baked into the client dashboard: However, I like using cartodb.js to customize events and interactions; in particular, I'd like to make styles ...
2
votes
2answers
493 views

Trying to use Python code and user defined inline parameters to build a SQL expression

I am trying to build a tool where the end user can run a model and make a selection from a feature class based on 3 different fields, calculate the total shape length, & export the results to a ...
2
votes
1answer
132 views

How to select all tenth in a field? [duplicate]

I want to select all tenth in a field! :( I know this way; "Km" = 10 or "Km" = 20 ... and so, but I am pretty sure theres other way. I am not SQL literate, but with your help in this forums I have ...
2
votes
1answer
865 views

Trouble formatting SQL expression for where clause in “Search layer by attribute” arcpy function

I am trying to pass a variable into a where clause in the "Search Layer by Attribute" arcpy function. I cannot seem to figure out the correct format for the SQL expression so that the function reads ...
2
votes
1answer
78 views

SQL / Select by Attributes query for “does not include text found in another specified field”

I'm using ArcMap 10.2 and I'd like to write a query to select features where Field A does not include text found in Field B. Specifically, Field A contains street numbers and street names. Field B ...
2
votes
1answer
64 views

Creating SpatiaLite View in QGIS?

Using these instructions (there are several other examples to find, all look the same and all of them seem to work) http://www.gaia-gis.it/gaia-sins/spatialite-cookbook/html/sp-view.html "Hand-writing ...
2
votes
3answers
285 views

How to use the SQL in select by attribute to select the entries for each values in certain field?

I am trying to use select by attribute for the table in ArcGIS, which is similar to the following picture (lets say): I want to select those entries for each A and B, which have maximum count. I ...
2
votes
1answer
467 views

Can't select geometry from Oracle DB using SQL

I'm trying to select the geometry field from an ArcSDE feature class in an Oracle 11g database (inside of a Python script via PyODBC). This is my query: SELECT SHAPE FROM USR.FEAT_CLASS This is ...