Tag Info

Hot answers tagged

6

Row_Number works well if you have data returned that will allow you to order your results in a unique way, that is, duplicates in the library_na field below will give duplicate OID values from the row_number function: create view testView as ( select row_number() OVER(ORDER BY library_na DESC) AS OID , library_na , geom from ...


3

Natural is a reserved keyword in PostgreSQL see full list and it has a special meaning in SQL JOIN "a shorthand for a USING list that mentions all columns in the two tables that have the same names" Write your query as SELECT "natural" FROM ... and it should work.


3

A query layer pointing to the original DB of features would allow you to generate a limited version of your dataset. A query layer uses full SQL, rather than the limited SQL available in a definition query. Your select statement should only include the desired fields instead of the default "Select *." ...


3

A definition query cannot hide columns of data. It is used to filter out rows of your data. For what you want to do I would either create a view of your feature class that doesn't include the columns you want to hide or if you are publishing this as a map service you can turn those columns off within the map document. Within ArcMap right click the layer ...


2

The general workflow to construct your polygon would be something like this: st_makepolygon(st_linefromMultipoint(st_collect(points.geom))) But you have to be aware that : your linestring has to be closed order of vertices does mater a rudimary sql querry to create a polygon from a set of points would be the following: with line as ( with mp as ( ...


2

I'd change my tables to something like this: CREATE TABLE regions ( id SERIAL PRIMARY KEY, name text NOT NULL, geom geometry(Polygon,4326) ) CREATE TABLE stations ( id SERIAL PRIMARY KEY, region_id INTEGER NOT NULL REFERENCES regions (id) ON DELETE CASCADE, name text NOT NULL, point GEOMETRY(Point, 4326) NOT NULL, CONSTRAINT fk_reg FOREIGN ...


2

You're getting an error because: activations.activationid is in a subquery that's been aliased to zz. Since the subquery doesn't really do anything you can flatten it and get INNER JOIN hat.activations.activationid zz ON (zz.activationid = ... However projectsgeom is on a diffrent nesting level too so the above will only change the syntax error. Either ...


1

I would add the constraint in a migration. Or, in something more pretty: a custom field. https://docs.djangoproject.com/en/1.6/howto/custom-model-fields/#writing-a-field-subclass Good luck.


1

Solution: Make sure that the SRID's in the geometries match. The SQL MSDN Documentation states: STContains always returns null if the spatial reference IDs (SRIDs) of the SqlGeometry instances do not match. And sure enough, I get null. When I defined the polys I gave them an SRID of 0, so they matched and the query worked as expected. In my second ...


1

I suggest few modification to your model and it should work, but in general you are on the right way. My data (hopefully as yours) include: Dealers point feature class and Sales point feature class. Each sale row has a dealer ID which relates the sale to its dealer. My modifications are: I used the Iterate by Row. Iterate by feature iterates through ...


1

SELECT sampling_points.the_geom_webmercator, sampling_points.suid, sample_point.suid FROM sampling_points, sample_point WHERE sampling_points.suid = sample_point.suid This query is generating two final columns called suid, so the ambiguity is not in the query itself but in the selected results, to sort this out, use alias: SELECT ...


1

I was able to create the correct answer, so have posted the correct Query here for future users who want to convert GIS data from two joined PostGres Table's to geoJSON I used information from the following pages to help me solve this: http://hashrocket.com/blog/posts/faster-json-generation-with-postgresql ...



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