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

learn more… | top users | synonyms

3
votes
1answer
240 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. ...
2
votes
3answers
1k views

Inserting polygon centroids into an additional column in PostGIS

I'm trying to calculate the x and y centroids for a table using ST_Centroid. The documentation says that it returns the geometric center of a geometry. I first made a column X: ALTER TABLE ...
2
votes
1answer
113 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
245 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
703 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
193 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
271 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
98 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
43 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
48 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
106 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
219 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
73 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
712 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
35 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
1answer
376 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 ...
2
votes
2answers
349 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
1answer
331 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
334 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
247 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
160 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
163 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
472 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
653 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
1answer
62 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
58 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 ...
2
votes
2answers
123 views

Can I use the LEFT (string) operator in ExecuteSQL()?

In QGIS I can use the LEFT operator on a dataset like this: LEFT( "Name2",3) ='031' What is the syntax for using this expression with ogr in python? Isn't this the right syntax? ...
2
votes
1answer
491 views

How to use GROUP BY with arcpy.da.SearchCursor in file geodatabase?

fc = "C:\Users\AuxS3-4\Documents\ArcGIS/Default.gdb\Malla" fields = ('OBJECTID','Sentido','Carriles','idMalla') sqlclause=(None, "GROUP BY Sentido") cursor = arcpy.da.SearchCursor(fc, ...
2
votes
1answer
91 views

How to split polygons with another polygon and inheritage data?

Let's say that we have two tables with polygons representing areas. Table A and Table B. Geometries from table A represents both the valid and invalid areas. And the geometries in table B represents ...
2
votes
1answer
493 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
2answers
443 views

Merge lines with the same 'name' attribute on QGIS?

I have a shapefile with a few thousand lines, each is a street segment. I'd like to merge all features with the same 'streetName' attribute into a single attribute. I only really care about the name ...
2
votes
1answer
566 views

How to get polygons from PostGIS table to PHP array best?

Currently I fetch polygons from a database table using ST_AsText(). The results are in text format, like the following. POLYGON((84 104,212 48,321 122,243 179,275 238,131 240,84 104)) I am not ...
2
votes
2answers
122 views

Trying to use an integer in a WHERE clause

I'm trying to use an integer value in a WHERE clause (looking at an integer field). expression = "'SERVICE' = 'Service' " expression = expression + 'AND ' expression = expression + "'GPS_TIME_TOTAL' ...
2
votes
1answer
187 views

SQL Command to convert pgRouting result to GeoJSON

My goal is to produce a Web UI for pgRouting using this tutorial. I have already imported my data from an OSM pBF to PostGIS using osm2po. Using the pgRoutingLayer plugin for QGIS, I was able to ...
2
votes
1answer
229 views

How to return nearest point to input lat/long with distance?

I am trying to write a query with PostGIS that returns one row where the geometry of that row is the closest to an input point. I would also like to include the distance between these points in the ...
2
votes
1answer
362 views

How to create a point table from a remote csv file?

The current approach that I am using to load a csv file into a PostGIS enabled database on a server is the following: -- create table CREATE TABLE points( id int, lat double precision, lng double ...
2
votes
1answer
824 views

How to delete records from multiple, similar feature classes by attribute values using an SQL statement?

I have several hundred geodatabase feature classes which all have the exact same fields. I need to go through each of those feature classes and delete any records which match an SQL query (the same ...
2
votes
1answer
751 views

How to extract data from a POINT column in PostGIS using JAVA (jdbc)

How do you get a value from a column which has a point data type. I've just switched over from MySQL to PostGIS. I have a column which is called point and is of type Point. I want to get that value ...
2
votes
1answer
625 views

Convert SqlGeometry data from SQL directly to Tiles / PNG file

I would like to know a basic method for 'exporting' SqlGeometry data from SQL Server 2008 to a Tile Server / PNG File generator. I already have a Silverlight Map application which uses a Custom Tile ...
2
votes
1answer
44 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. so far I tryed to modify the ...
2
votes
1answer
204 views

is there any direct access to Mapbox data? (similar to CartoDB's sql api)

I've been asked to add a form to a map that will write data in the background (e.g., ajax to a magic file that does datawrites and some emailing, notifications etc) and a new marker will be added to ...
2
votes
1answer
187 views

How to Buffer based on SQL expression?

Is there any way to optimize this code? It creates two buffer feature classes based on two different fields in the shapefile attribute table import arcpy from arcpy import env ## Set Workspace ...
2
votes
2answers
311 views

What is faster for geoprocessing (intersect) big shapefiles?

I have 200 Mb shapefiles of polylines downloaded from OSM. I've been working with QGIS 2.0 in Ubuntu 64 Bit 8 Gb RAM to make some geoprocessing tasks and it's taking 2 days for processing. Once I've ...
2
votes
1answer
86 views

Tiger geocoder / postgis - get county from point

Looking for some help with getting the county for a lat / lon. I have postgis / tiger geocoder installed and can reverse look up a point and get the addresses. I would like to get the county the ...
2
votes
1answer
267 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 ...
2
votes
2answers
146 views

Problem with SPIT (error while importing layers to POSTGIS)

I was attempting to import a polyline roads layer into postgis, using both SPIT in QGIS 1.7 and postGIS GUI for this purpose. I have received errors through both processes as described here: SPIT ...
2
votes
1answer
337 views

How do I select * rows of multiple columns in one query using Select by Attributes within ArcMap?

I am trying to convert a psql query string into arcpy formatting for an UpdateCursor where expression parameter: '''SELECT ...
2
votes
1answer
154 views

How to create a network with all the track points

I have created a sample network through the use of gps visualizer. Than I used the ogr2ogr function to import this data in my database. Now I have a table which holds all the track points of these ...