Tag Info

Hot answers tagged

10

'foo' is the name that has been assigned to the sub-selection. It has no function other than being syntactically necessary - a sub-selection in PostgreSQL must have a name assigned to it, but it does not matter what the name is. 'foo' was likely chosen because it is a common meaningless placeholder word used by programmers, similar to 'lorem ipsum' used by ...


3

Your Line ... WHERE ST_geometrytype(geom) = ST_Point; Indicates that ST_Point is a column since it is not in quotes. I see here also a comment from @Vince that is saying basically the same thing that I am. Since ST_Point is not quoted in your expression is is trying to evaluate what is in ST_geometrytype(geom) against another field/column and it can't ...


3

I would use Python's itertools and a SearchCursor for a very efficient way to find the spatial relationships you are after. You can incorporate the geometry methods overlaps, contains, and equal to get at the geometry properties. Start off by creating a function to better organize the workflow and for repeatability def findOverlaps(x): Open a search ...


3

There is an easier and more efficient way of doing this. Works for both PostGIS 2.1 and 2.0. Just use the ST_ValueCount function. http://postgis.net/docs/manual-2.1/RT_ST_ValueCount.html that will give you both the pixel value and number of pixels that have that value. So would be for your case SELECT DISTINCT (pvc).value FROM (SELECT ...


2

The following query appears to do a reasonable set of voronoi polygons starting from the Delaunay Triangles. I'm not a big Postgres user, so it can probably be improved quite a bit. WITH -- Sample set of points to work with Sample AS (SELECT ST_GeomFromText('MULTIPOINT (12 5, 5 7, 2 5, 19 6, 19 13, 15 18, 10 20, 4 18, 0 13, 0 6, 4 1, 10 0, 15 1, ...


2

I have an idea what may work for you. It is going to be based off some assumptions, but it would help narrow down your list of possible identical features. This would not be an automated process, but it would require manually looking at the duplicates. Based off the comments, it seems like the automated tools don't compare attributes so this would help ...


2

You need to cast from the output of ST_MAKE() which is a geometry to the geography data type. This casting is done by using the :: operator. The following command should work: UPDATE customer_campaign set geom=ST_MakePoint(customer_campaign.long, customer_campaign.lat)::geography;


2

This reminds the old quiz: 5 person will shake hands with each other. How many handshakes will there be in total? Is it 20 because everybody must shake hands with 4 other person? No, because if Mike shakes hands with John, John does not need to shake hands with Mike for another time. Let's assume that you have 5 points. Point number 1 does not shake hands ...


2

Yes, you will need to use the external procedure agent if you want to access the XML columns in the SDE.GDB_ITEMS_VW views. By default, the XML columns in the GDB_* tables are stored using ESRI's XML implementation. The functionality to convert these to a CLOB is included in the ST_Geometry library, which is accessed using the external procedure agent. ...


2

I suggest this: SELECT TOP 3 * FROM MyTable WHERE NOT [Field2] = '%Variable%' ORDER BY [Field1] Example Let's start with an unordered table: +------------------+------------+ | rainbow_sequence | color_name | +------------------+------------+ | 2 | orange | | 3 | yellow | | 1 | red | | ...


1

I tried your commands and changed filename raster, to filename char(250), , assuming you meant to use a string for that (and declaring it as a raster is a typo??). The commands seem to work without -C. With -C, I got some warnings/notices about numeric field overflow and no_data. But I guess that's just something in my data. What I tried is: echo ...


1

All four of the databases you listed have the capability to support the type of query you're asking about. SQLite and Postgres through extensions (Spatialite & PostGIS respectively), mySQL and MariaDB have some basic GIS support built in natively. As to which to use, it depends on your needs. Personally I go with SQLite for simple in house ...


1

SELECT ST_X(pts.geom) as lon, ST_Y(pts.geom) as lat, poly.attribute FROM points pts, polygons poly WHERE ST_Intersects(poly.geom, pts.geom); where it is assumed that the attribute you want from the polgons table is called attribute. For non-trivial table sizes, you will want a spatial index on the geometry columns of polygons/points tables.


1

Select points.*, polygons.* from points inner join polygons on st_intersects(points.geom,polygons.geom);


1

When you apply an SELECT SQL query from the CartoDB editor, a green bar appears as a header with two options: create table from query and clear view. Moreover, your table view gets updated with the contents that fulfill the applied SQL query. However, when you expand this query by adding some additional filtering parameter (or replace the applied query ...


1

In OGR, the elevation data gets stored in a point layer called track_points. You can do "normal" ogr2ogr operations preserving the elevation data like this: ogr2ogr -f GPX output.gpx input.gpx waypoints routes track_points However, that fails on your ST_UNION command because you requested a Multilinestring (which has no vertex elevation data by ...


1

If you are into Java and want Flexibility, Graphhopper is an excellent alternative. Peter, the project lead, actively encourages extensions and changes and the community is very responsive. Graphhopper works differently to Pgrouting (I use both) and it is extremely fast. Instead of using a PostGis database, Graphhopper builds its graphs direct form the .pbf ...


1

Open your GeoServer Admin Instance at http://yourhost:8080/geoserver/web/ and log in .. To use SQLViews, you must have a database configured and available with data in it .. usually PostGreSql, and you must have already configured a Database DataStore to use SQL Views. Select layers -> Add new Resource (green + button at the top) In the drop down list of ...


1

You can do this in SQL using a spatial self join. You don't state which SQL dialect you are using, so this example uses Postgres/Postgis, but it could be easily adapted to Oracle or SQL Server. Assuming a table called buildings, with geometry stored in a column called geom: SELECT a.id, b.id from buildings a, buildings b WHERE ST_INTERSECTS(a.geom, ...


1

If you had the 3 different columns in your CartoDB, you could update it with the SQL API. So if you wanted your CartoDB table (we'll call it crocs_table) looked like this: num_croc_male num_croc_female total_crocs Row1: 5 10 15 You could update it it using the SQL API like so: ...


1

There are a couple of options. Option 1 You could do the query exactly like you have it, then add your linestring using GeoJSON on the client. You'd use CartoDB.js to draw you polygon layer and then use Leaflet to add the GeoJSON layer on top. Option 2 Use a multilayer visualization. The bottom layer you could have as you polygons (styled accordingly. ...



Only top voted, non community-wiki answers of a minimum length are eligible