Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have two tables:

CREATE TABLE periphereies
(
  gid serial NOT NULL,
  per character varying(60),
  geom geometry(MultiPolygon),
  CONSTRAINT periphereies_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);

CREATE TABLE archaia_theatra
(
  gid serial NOT NULL,
  "ΘΕΑΤΡΟ" character varying(254),
  "X" numeric,
  "Y" numeric,
  geom geometry(Point),
  CONSTRAINT archaia_theatra_pkey PRIMARY KEY (gid)
)
WITH (
  OIDS=FALSE
);

First of all i am trying to write a query that return's me all the periphereies record's that contain at least one archaia_theatra.geom point record.. Then i want to select the periphereies which contains the maximum number of archaia_theatra (compared with all the other perifereies records)

I am trying this:

CREATE OR REPLACE VIEW per_max_theatra AS
SELECT count(p.per) FROM periphereies AS p JOIN archaia_theatra AS a ON
ST_Contains(p.geom, a.geom) ORDER BY count(p.per) DESC LIMIT 1

but it's not working

Any help?

Thx in advance

share|improve this question
    
what is error? btw your view is only count(p.per) without any relation to p sso , maybe add p.id there or something ? –  simplexio Jun 15 '13 at 21:22
    
@simplexio SELECT * FROM per_max_theatra returns 40 but 40 is all the archaia_theatra records (and it's a wrong answer) –  Chris Pappas Jun 15 '13 at 21:39
1  
see @micha answer. select p.geom, count(p.per) ... group by a.geom. would return all a.geoms and count of p.geom which are inside of p.geom –  simplexio Jun 16 '13 at 20:03

1 Answer 1

You probably need a GROUP BY p.gid in a WHERE clause.

share|improve this answer
2  
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. –  Fezter Jun 16 '13 at 20:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.