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

learn more… | top users | synonyms

2
votes
2answers
676 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
172 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
255 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
97 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
232 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
2answers
82 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
187 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
668 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
1answer
321 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
311 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
296 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
320 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
240 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
152 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
160 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
460 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
646 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
41 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
53 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
111 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
445 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
89 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
408 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
392 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
532 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
116 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
183 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
221 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
355 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
793 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
721 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
611 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
165 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
182 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
296 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
79 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
238 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
142 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
331 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
151 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 ...
2
votes
1answer
119 views

Join Zip Codes Table to Nearest City from Second Table

I have two tables, siteareas and ziplookup. The former contains large cities in the US; the latter all US zips with lat/lngs. I have a foreign key into the former table in the latter: sitearea_id. I ...
2
votes
1answer
248 views

Creating a model to run a complex query

I'm just a rookie when it comes to creating models in ArcGIS. I have two shapefiles from which I need to calculate an AVG and a MAX erosion rate for individual properties. The two shapefiles are: ...
2
votes
2answers
973 views

Convert RT90 into WGS84

Goal: Gain WGS84 decimal to locate some cities in Google Map. Problem: I don't know how to convert the value of the RT90 coordinate system into WGS84 in SQL Server.
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 ...
2
votes
2answers
58 views

Finding Polygons which do not touch any other polygon in a single oracle database table

I currently have an oracle table of approximately 100,000 records - all of which are polygons. I want to find all the polygons which do not touch any other polygon within the same table. There are a ...
2
votes
1answer
63 views

Conditional SQL Query

I've been running a code that searches a desired field in a feature class and performs an specified SQL query based on whether the field is string or double. The variables are just temporary fillers ...
2
votes
0answers
32 views

Multiple Cursors

Below is the sql statement which I have explained below. Declare Cursor c1 is select store_number ,0 as total_area,0 as BG_ID ,0 as store_geom from table1 where CLIENT_ID= 1 and ORG_ID ...
2
votes
2answers
80 views

shp2pgsql mangles date field — quotes data as if it were char or varchar

I'm attempting to import a shapefile into postgis via shp2pgsql. Among the fields in the shapefile's .dbf file is a date field with format yyyymmdd, which is a valid date format for postgresql. ...