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

learn more… | top users | synonyms

3
votes
1answer
257 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
310 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
218 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
163 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
646 views

Routing with multiple points

I am using postgreSQL, postGIS and pgrouting. Now I am really confused on how am I going to find the shortest path between multiple points. I know how to find it between two points through dijkstra ...
3
votes
1answer
176 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
880 views

Python script for constructing a WHERE clause from user input

I'm trying to construct a where clause from user input that will eventually be passed through as a SQL statement in the select tool or the select by attributes tool. I've started a script thats been ...
3
votes
1answer
266 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
2answers
1k views

Adding rows of data to a non-spatial table in ArcMap with a SDE SQL Server for Metadata purposes

I'm attempting to use several non-spatial tables to better organize the large set of feature classes I continually add to a SDE Geodatabase (SQL Server Express). So I have several tables setup and ...
3
votes
0answers
71 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
100 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
86 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
0answers
247 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 ...
2
votes
1answer
117 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
213 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
3answers
97 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
335 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
2answers
757 views

ArcObjects: Alternative workflow to using a SQL query with over 1000 values in IN statement

I have a ListBox listing the unique values of a column in a feature class; the user can select any number of values and click a button to then zoom to the set of features matching those values. A ...
2
votes
3answers
408 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
382 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
1answer
103 views

how do I select more than one features from raster attribute table

for ex: "lc_2001" = 21, 22, 23, 24 AND "lc_2006" = 11, 31, 41, 51
2
votes
2answers
234 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
64 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
101 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
2answers
218 views

How to get the data type of each column from a postgis table

I need to get the column data types of all columns in a table, including the geometry types. What I wanted to know is if there is a function or SQL that gives something like this: column_name | ...
2
votes
2answers
148 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
1answer
864 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
254 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
79 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
860 views

What is the proper way to delete an SQL Server database in order to delete an SDE geodatabase?

I'm new to this SDE stuff and would like to know the best way to cleanly delete an SQL Server database in order to delete the SDE geodatabase. Is it as easy as right clicking on the database in SQL ...
2
votes
1answer
1k views

How do I format 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
2answers
1k views

Error adding data to Geodjango database: parse error… within geometry

I'm trying to insert some values into a Geodjango database, but getting errors. This is the table: class Place(models.Model): placeid = models.IntegerField(primary_key=True) structidx = ...
2
votes
2answers
51 views

PostGIS SQL NOT EXISTS Statements? [closed]

I'm trying to query my PostGIS database for ID of people who did X but did not do Y. I've attempted the query below, but it does not return any results. I'm very new to SQL so I may be overlooking ...
2
votes
2answers
522 views

CartoDB- Passing selected features bounds to map center parameter (Zoom to selected features)

Looking to build a light-weight viewer for some data we have hosted on CartoDB. I'm looking to have a user input their address into an input, pass a query to CartoDB and have the map zoom/pan to the ...
2
votes
2answers
785 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 ...
2
votes
1answer
444 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
446 views

How do I use the SearchCursor where_clause to get a max value from a column?

This is my first time asking a question on StackExchange. I hope I phrase my question and describe my attempts to understand my problem well enough! While working with SearchCursor, I'm trying to ...
2
votes
1answer
397 views

Select top 5 most items in a 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 geodatabase where i have a price field. Prices are not necessarily ordered but can ...
2
votes
1answer
260 views

Direct DBMS sql queries and ArcSDE geodatabase

i want to make updates from Arcobjects on several tables stored on a ArcSDE 10.1 versionned geodatabase based on Postgresql 9.0.5 , none of these update require spatial data , and making these updates ...
2
votes
1answer
175 views

Postgis nested query trouble

I am having difficulty writing a nested postgis query. I have a single table from which I need to create a view in PostgreSQL. I have created the view with the following command create view ...
2
votes
1answer
169 views

SQL Plus error using sdeversion

I'm new to SQL Plus and I have a question. I've created several versions of a feature class located on a SDE database. I want to use ArcSDE Administration Commands found here but I'm getting an error ...
2
votes
2answers
496 views

Parameterized insert query to PostGIS fails

I have a PostGIS database table that I need to insert data into. I'm using Python 3 and the pg8000 interface to Postgres. Somehow, parameterized inserts do not work. If I do iq="INSERT INTO ...
2
votes
3answers
673 views

Definition Query to Python script on Oracle 9i and ArcGIS 9.3.1

SUBTYPECD=1 AND SERIESNAME NOT LIKE '%-%' AND OPERATIONALSTATUS IS NULL OR SUBTYPECD=1 AND SERIESNAME NOT LIKE '%-%' AND OPERATIONALSTATUS = 976 The above is my def query in ArcMap against ArcSDE ...
2
votes
2answers
114 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, ...
2
votes
1answer
52 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 ...
2
votes
1answer
60 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
51 views

Find nearest distinct features to a point

I am trying to select a single distinct features closest to an XY point in PostGIS. So I can find a single record for each distinct code by using: Select Distinct ON (code) * FROM table; Which ...
2
votes
1answer
82 views

Cannot load postgis layer from sql string in pyqgis

I'm trying to load a layer using the following code in qgis python console: sql = "(select the_geom, ID from mytable)" uri = QgsDataSourceURI() ...
2
votes
1answer
171 views

Query to select MIN and MAX values by groups

In ArcMap 10 i'm working in a Personal Geodatabase where the points (see screenshot below) represent the number of each house in the street. Each point has an ObjectID. This ObjectID is an unique ...
2
votes
1answer
84 views

Get list of all tables in CartoDB account with SQL statement?

How can I get a list of all available tables in my own authenticated CartoDB account with a SQL statement? This returns null: SELECT table_name as t FROM information_schema.tables WHERE table_schema ...