Advertisement
Guest User

Untitled

a guest
Jul 27th, 2016
55
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.00 KB | None | 0 0
  1. CREATE TABLE adresspoint
  2. (
  3. id character varying(3999) NOT NULL,
  4. "position" geometry(PointZ,25832)
  5. CONSTRAINT pk_adresspoint PRIMARY KEY (id)
  6. )
  7.  
  8. CREATE INDEX adresspoint_idx_position
  9. ON adresspoint
  10. USING gist
  11. ("position");
  12.  
  13. CREATE TABLE house
  14. (
  15. id character varying(3999) NOT NULL,
  16. adresspointid character varying(3999),
  17. CONSTRAINT pk_house PRIMARY KEY (id)
  18. )
  19.  
  20. CREATE INDEX house_idx_adresspointid
  21. ON house
  22. USING btree
  23. (adresspointid COLLATE pg_catalog."default");
  24.  
  25. SELECT DISTINCT house.id
  26. FROM house
  27. LEFT JOIN adresspoint ON adresspoint.id = house.adresspointid
  28. AND st_contains(st_makeenvelope('562827','6326412','563365','6326590',25832),adresspoint.position)
  29. OFFSET 0 LIMIT 100
  30.  
  31. SELECT DISTINCT house.id
  32. FROM house
  33. LEFT JOIN adresspoint ON adresspoint.id = house.adresspointid
  34. AND st_contains(st_makeenvelope('562827','6326412','563365','6326590',25832),adresspoint.position)
  35. WHERE adresspoint.id is not null -- ***change is here***
  36. OFFSET 0 LIMIT 100
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement