Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have two tables:

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

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)

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:

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 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
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

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

share|improve this answer
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


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.