Hot answers tagged

5

An sql where clause can't incorporate sql functionality such as MAX or MIN because your input can only be what proceeds WHERE in an sql. Note how in the Select By Attribute window the first part of the SQL query is already provided: The make feature layer sql where clause behaves in the same fashion. If you want to create a feature layer with a minimum or ...


3

This can be done a bit more simply with json_build_object in PostgreSQL 9.4+, which lets you build up a JSON by supplying alternating key/value arguments. For example: SELECT json_build_object( 'type', 'Feature', 'id', gid, 'geometry', ST_AsGeoJSON(geom), 'properties', json_build_object( 'feat_type' : feat_type, ...


3

Assuming you have two tables "a" and "b". Table "a" is the table to be cut. CREATE TABLE a ( id serial PRIMARY KEY, geom geometry(MultiPolygon,31370) ); Table "a" is of type multipolygon because we don't know yet if some polygons will be separated in multiple parts afther the "cookie cut". CREATE TABLE b ( id serial PRIMARY KEY, geom ...


3

As commented by @MichaelMiles-Stimson: Intersect the layers. Spatial Join would work then you only need to select where VILLAGE <> VILLAGE_1. This will give you the locations.. are you after the specific points? if so use geodatabase feature class (static OID) then both OID (point and polygon) are in the attribute table, join by attributes to ...


2

Instead of a marker you can generate a buffer, setting each category (metro, tam and bus) to a numeric field (1000, 750 and 500). Then run the following queries: SELECT cartodb_id, ST_Transform( ST_Buffer(the_geom::geography, 1000)::geometry ,3857 ) AS the_geom_webmercator FROM table_name WHERE field_name ilike 'metro'; SELECT ...


2

The shapefile specification states that the dBase file is limited to two gigabytes (2^31-2 [-2 not -1, because the records are counted in short integer chunks)]). Some open source utilities can handle one overflow (2-4Gb). 14Gb is far too large. Your choices: Break the file into 10 1.4Gb chunks Reduce the file width to 1900-2000 bytes (this assumes the ...


2

If I am understanding you right you started out with an NLCD raster and a polygon feature class of your grids. You then converted the raster to polygon within each of your grids cells to yield polygons with the corresponding NLCD land cover class. I would redesign your workflow as follows: Before converting to polygons I recommend using the Zonal Statistics ...


2

You can do your own function in PostgreSQL like this (Example taken from the docs) : CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT); INSERT INTO foo VALUES (1, 2, 'three'); INSERT INTO foo VALUES (4, 5, 'six'); CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS $BODY$ DECLARE r foo%rowtype; BEGIN FOR r IN SELECT * FROM ...


1

Not sure what do you mean with "a house next to it" but here an example of what you can try: SELECT a.id, a.geometry, 'T'::text as type FROM houses a, houses b WHERE ST_Intersects(a.geometry,b.geometry) AND a.id != b.id Could be done with other spatial operator (ST_DWithin could be a better candidate). Better with Gist index on geometry field.


1

There are two views that you need to check geometry_columns and geography_columns that will provide you with a list like: "ian";"public";"coastline";"geom";2;27700;"MULTILINESTRING" "ian";"public";"motorway";"geom";2;27700;"MULTILINESTRING"


1

This is WKB format wichs is a binary representation of the geometry. To select the geometry in GeoJSON format for example you can use : ST_AsGeoJSON()


1

Read your CSV file and create a vars_list list of tuples to insert. Reading the CSV file is off topic, but it should have a structure something like this: vars_list = [ (lng1, lat1, lng2, lat2), # first record (lng1, lat1, lng2, lat2), # second ... ] Then insert them all at once with executemany, like this: sql = '''\ INSERT INTO ...


1

The simplest answer is to use a CQL filter in the WMS request to restrict which landuse types are drawn on the map. There is a full tutorial available to get you going. However in your case it would be something like: http://...../geoserver/wms?....&CQL_FILTER=landuse='grass' when you want to filter by grass or more complex options like: ...


1

In the past I've overcome this by replacing the whole where clause with a parameter, and then set the default to 1=1 SELECT * FROM areas WHERE %query& you can then supply the full clause e.g. landuse = 'grass' within the parameter declaration in the URL. I appreciate this is a slightly unsatisfactory, and there would be more elegant solutions ...


1

Yes it's possible to do this in python. Check out the documentation on the Make Query Layer tool http://pro.arcgis.com/en/pro-app/tool-reference/data-management/make-query-layer.htm You can also use the make query table tool (http://pro.arcgis.com/en/pro-app/tool-reference/data-management/make-query-table.htm); if you specify a shape field the query table ...


1

Look at creating a set of valid animal/year values . feed that into the loop. Using arcpy, run frequency analysis on the data, using the two fields as the frequency fields. The resulting rows will be the valid combinations. With a cursor on the table, read the animal/year into your query. freqFields = ['ANIMAL', 'YEAR'] freq = arcpy.Frequency_analysis(data, ...


1

I studied your questions, because sometimes I need it.. My suggestion is depends of your format: Case A: Shapefile Create a new shapefile using "Dissolve" Tool Case B: PostGIS: Create a View using "Group by"



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