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

learn more… | top users | synonyms

0
votes
2answers
50 views

How to use SQL within python in ArcGIS Labels

I'm trying to create labels across a one-to many relationship and have tried a simplified version of this method. My version looks like this: def FindLabel ( [NAME_V2] ): import arcpy ...
1
vote
2answers
112 views

Definition Query to hide attribute data

SQL help please! I've tried searching, but can't find the help I need. This is for ArcGIS Advanced 10.2. I have a parcel layer with all kinds of info that the general public shouldn't be able to ...
2
votes
1answer
31 views

TSQL Geometry Query Requires conversion to string

If I specifically define some polygons and check for containment, I get a correct result: DECLARE @g geometry; DECLARE @g2 geometry; SET @g = geometry::STGeomFromText('POLYGON((407305 100826.5, ...
1
vote
0answers
32 views

How to Load SQL Geometry Data on OpenLayers3?

I am New in OpenLayers 3. I have to load Geometry data from sql to my OpenLayers map. This is my code : var tileLayer = new ol.layer.Tile({ source: new ol.source.MapQuest({ layer: 'osm' }) }) var ...
0
votes
1answer
60 views

WMS layer connected to SQL database

It is possible to connect a WMS layer to a SQL database or other type of database? Example: I have created an application with Geoserver and OpenLayers. Now I can see the data from attribute table. ...
0
votes
0answers
20 views

Update Statement in PostGIS ERROR

I am trying to update a column in PostGIS using information from a column in another table where they spatially intersect. I looked up how to do this on the PostgreSQL tutorial and I think that I ...
1
vote
1answer
42 views

Using a feature iterator Value variable in a SQL expression of select by attribute

I am using Iterate feature selection to select dealers one by one as input features to the Near tool. I'm using Select by Attributes to select sales points from that same dealer. How do I sync the ...
0
votes
3answers
186 views

How do I reformat numbers to only show a certain number of significant digits in CartoDB?

I need to round numbers that are in my table in CartoDB, so that in the Map visualization infowindows I only show results to two decimal digits rather than the long decimals that are currently ...
0
votes
1answer
25 views

Creating Shapefiles from MS SQL using ColdFusion/Java?

A client wants to export, from their MS SQL server, data into Shapefiles. I've looked around for a starting ground and have heard Ogr2Ogr mentioned and it seems intimidating. So I was wondering if ...
0
votes
0answers
28 views

How do I insert a Query Result into an existing Table?

I am attempting to create a map that displays the Service Area for Firestations within Toronto, In order to do this I need to identify the Length of all pathways. I tried using the calculate geometry ...
0
votes
0answers
16 views

How to select using ST_MakePolygon from a database using points of a polygon

I want to write a sql command that can select within a polygon that has many points of reference ie a boundary of an area. I was able to use this command SELECT * FROM testtable WHERE the_geom ...
1
vote
1answer
21 views

How do I query in natural=strait from OSM databse?

I'm trying to query in straits, fjords, and bays from a PostGIS Openstreemap database. The straits I'm looking for are tagged natural=bay, natural=strait, and natural=fjord. I used the following ...
6
votes
3answers
4k views

Select features by attribute if in Python list

I believe this will amount to largely a sql syntax question. Essentially, I am trying to complete a select by attribute in Python but based on the query of whether an attribute is present in a list. ...
3
votes
3answers
2k 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 ...
0
votes
1answer
16 views

What is the best strategy to add a custom constraint to a geo-django table?

What is the best strategy to add a custom constraint to a geo-django table? This is the corresponding sql-statement: ALTER TABLE my_table ADD CONSTRAINT enforce_valid_geom CHECK ...
1
vote
0answers
15 views

Free tutorials for beginners in PostgreSQL, PostGIS and SQL? [duplicate]

I've been cruising youtube videos and various sites trying to pick up information on working with postgresql, postgis, and sql. I would love a few recommendations for quality tutorials and sites. ...
1
vote
2answers
200 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
2answers
96 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 ...
0
votes
1answer
33 views

UPDATE a value field for area of the geom and convert sq m to acres using cartodb

I'm trying to use the SQL commands in CartoDB to UPDATE a value field that shows total acres of the area of the_geom. My SQL is pretty poor/entry level and has some issues: UPDATE table_name SET ...
1
vote
0answers
54 views

ArcSDESQLExecute to query and update from multiple SDE Databases

I am trying to use ArcSDESQLExecute to return the results of a join and then write the return query into a Feature Class located in a different database. Using Microsoft SQL Server and Arc10.2.2 Is ...
0
votes
1answer
53 views

CartoDB table join error: Column reference is ambiguous

I've got a CartoDB question that seems dumb b/c the layer is correctly rendering. Anyway, I have two layers, named sampling_points sample_point I'm trying to inner join the sample_point data to ...
0
votes
0answers
33 views

How to Transform Many Projections Into One With SQL Server

I know very little SQL Server, but am quite familiar with Arc and Python. The data I want to transform is point data which is in a single table [ID], [X] [Y], [COORDSYS]. There are 5 possible ...
2
votes
2answers
129 views

PostgreSQL/PostGIS Geom to JSON with Inner Join to another table

I am currently producing JSON from a database successfully using the following query: SELECT row_to_json(fc) FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features FROM ...
0
votes
1answer
37 views

Update the_geom field with polygons from a different table using one common field

Using CartoDB SQL API I'd like to: Update the_geom field in a table that has no spatial data by selecting the polygons from a table with spatial data and joining based on one common field.. In this ...
1
vote
0answers
107 views

ESRI JavaScript API Web Application and SQL Queries in ASP.NET?

My workflow (and security restrictions) for a new html/javascript based web application is making me think that I could program an ASP.NET application, inside which I load the 'map' and the 'legends' ...
1
vote
1answer
463 views

Creating SQL query in ArcGIS Explorer SDK?

I am using the ArcGIS Explorer SDK and have run into an issue surrounding a query I would like to perform with SQL. I believe my query is built wrong or perhaps I am looking at the wrong fields. Would ...
7
votes
2answers
3k views

SELECT TOP query in ArcGIS Desktop?

Is there a way to use a SELECT TOP (n) within ArcGIS desktop? Is appears that since the ArcGIS query builder is effectively building the SQL statement after the WHERE clause, that this may not be ...
2
votes
1answer
501 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 ...
3
votes
2answers
82 views

How to reproduce FME Change Detector results in PostGIS?

we would like to use the function change detector from FME in PostGIS. We had a try with the following SQL-Statements but the function doesn´t work. There are no results in the end. The single ...
1
vote
2answers
76 views

Load multi CSV files from local disc to Postgres

I want to load a lot of CSV files from a local disc in an already created postgres database. Which is the easiest way to do that? By using SQL or Java? I suppose that I need a loop for doing that, but ...
17
votes
5answers
3k views

PostGIS: Select features that do NOT intersect

This seems to me like such a simple question (and it probably is) yet I cannot seem to find an example that gives me the answer. Using PostGIS, I just want to select points that fall outside of ...
0
votes
0answers
44 views

ArcGIS Workflow Manager Query - Find Duplicate Job_ID

We have a query in ArcGIS Workflow Manager 10.2 shown below that shows "All Assigned Jobs". Due to the nature of our workflows (parallel workflows), some jobs are duplicated more than once in the ...
1
vote
1answer
65 views

ogr2ogr command : chaining -clipsrc and -sql filters?

I'am currently doing a clip then SQL query to select the biggest cities out of Natural Earth data via 2 commands : ogr2ogr -clipsrc 1 52 7 49 ./tmp.shp ...
4
votes
1answer
178 views

SQL-query in arcpy - SELECT WHERE feature between two values

I need to select features between two values in python with arcpy. However, whenever I try to run the code below, it returns with error message "An invalid SQL statement was used". whereclause = ...
2
votes
1answer
122 views

Model Builder Query by date

I've created a model that does several spatial joins based off of a lat/long in a table. In the table, I have a "Modified_date" which gives year, month, date, time. This model is set to run ...
0
votes
1answer
59 views

Find nearest node function

I am trying to get work the next function but i don't know if it is incomplete because the query SELECT * FROM pgr_nearest_node1('jaen',-3.75,37.779); does not provide any results. Here is the ...
0
votes
1answer
102 views

pgr_drivingdistance wrapper

Pgr_drivingdistance function works as : SELECT seq, id1 AS node, cost FROM pgr_drivingDistance( 'SELECT id, source, target, cost FROM edge_table', id, distance, false, ...
0
votes
0answers
33 views

Syntax error drivingdistance

I am new in sql and i am trying to get that works a subquery with pgr_drivingdistance and pgr_pointsaspolygon but i get the next error: subqueries in FROM must to have an alias,for example, FROM ...
0
votes
1answer
93 views

PgRouting function wrapper

I'm trying to adapt the pgr_fromAtoB function to pgr_fromAtoMult replacing pgr_dijkstra function to pgr_drivingdistance.Here is the code: CREATE OR REPLACE FUNCTION pgr_fromAtoMult(IN tbl character ...
0
votes
0answers
74 views

GeoServer Style referencing SQL Server data store

I have a SQL server (2012) based data store in GeoServer (version 2.6) . For better readability I created a spatial layer with a whitespace in a column (attribute) name. Trying to use a style with ...
3
votes
1answer
141 views

PostGIS multiple conditional statement on spatial join

I want to make a multiple conditioned spatial query in PostGIS 2.0/PostgreSQL 9.3., OS Windows 7. There are 2 tables, cadastre (Polygon) and roads (Linestring). Query: Select the sum (one number) of ...
4
votes
0answers
97 views

Spatial query too slow to handle bigger OSM extracts

I'm trying to compute some statistics for OSM data using PostgreSQL 9.3.5 and PostGIS 2.1.4. I started with a small bavaria extract which I downloaded from Geofabrik. The db schema is the normal API ...
0
votes
1answer
136 views

MapInfo Calculating the number of Points that Fall within a Boundary

I have two layers in MapInfo (points and polygons). I need to select points which fall within polygons where the income > 1500 I tryed this one but not working: ...
1
vote
1answer
39 views

Using MOD within a GROUP BY subquery on point dataset

I have a dataset of 200 points (in shp/f gdb/p gdb format) in ArcGIS. I am trying to use the MOD() function within a GROUP BY subquery as a definition query on the layer to thin out the data. What ...
1
vote
1answer
121 views

Merging multiple tables using SQL in CartoDB

I have 5 separate tables in CartoDB each with a varying number of columns, but all containing these 4 columns (the_geom, name, type, unit_code). I want to merge them all into 1 new table and get rid ...
0
votes
0answers
25 views

SQL query syntax for NDVI grid in ArcGIS for Desktop?

I am trying to extract by attribute pixels with a Value >0 (i.e., the vegetation in an NDVI grid file) in ArcGIS. Every time I try to extract by attribute (spatial analyst tools/extraction), my SQL ...
1
vote
1answer
90 views

PostGis - BBox -vs- ST_intersects Queries

CartoDb Recommend here that the following query is faster than an St_intersects Query SELECT * FROM {table_name} WHERE the_geom && ST_SetSRID(ST_MakeBox2D(ST_Point(-73.9980, 40.726), ...
0
votes
2answers
99 views

Link to another table in CartoDB infowindow

I have two layers in CartoDB, one with spatial data, school_count, and one without, heisman_winners. I want to visualize my spatial layer but when a user clicks on a feature I want it to display data ...
0
votes
1answer
31 views

Walking-Driving area with Leaflet,OpenLayers

Could be possible to get a walking or driving distance area using pgr_drivindistance,pgr_alphashape and/or pgr_pointsaspolygon when an user click any location on map with Leaflet or OpenLayers?. I ...
1
vote
0answers
75 views

SQL query works in PostgreSQL but not in GeoServer SQL View

I can't find a solution for this problem after a couple of hours of research. The query is: SELECT searchaddress('some string'); FETCH ALL IN mycursor; The function searchaddress returns a ...