sql - How to filter the max value and write to row? -

-

postgres 9.3.5, postgis 2.1.4.

i have tow tables (polygons , points) in database.

i want find out how many points in each polygon. there 0 points per polygon or more 200000. little hick following.

my point table looks following:

x    y    lan 10  11    en 10  11    fr 10  11    en 10  11    es 10  11    en - #just demonstration/clarification purposes 13  14    fr 13  14    fr 13  14    es - 15  16    ar 15  16    ar 15  16    ps

i not want count number of points per polygon. want know occuring lan in each polygon. so, assuming each - indicates points falling new polygon, results following:

polygon table:

polygon    count   lan 1          3       en 2          2       fr 3          2       ar

this got far.

select count(*), count.language language, hexagons.gid hexagonswhere    hexagonslan hexagons,         points_count france   st_within(count.geom, hexagons.geom)   group language, hexagonswhere   order hexagons desc;

it gives me following:

polygon    count     language 1          3         en 1          1         fr 1          1         es 2          2         fr 2          1         es 3          2         ar 3          1         ps

two things remain unclear.

  1. how max value?
  2. how cases treated there chance max values identical?

answer 1.

to common language , count per polygon, use simple distinct on query:

select distinct on (h.gid)        h.gid polygon, count(c.geom) ct, c.language   hexagonslan h left   join points_count c on st_within(c.geom, h.geom) group  h.gid, c.language order  h.gid, count(c.geom) desc, c.language;  -- language name tiebreaker

but data distribution describe (up 200.000 points per polygon), should substantially faster (hoping make better use of index on c.geom):

select h.gid polygon, c.ct, c.language   hexagonslan h left   join lateral (    select c.language, count(*) ct      points_count c     st_within(c.geom, h.geom)     group  1    order  2 desc, 1  -- again, language name tiebreaker    limit  1    ) c on true order  1;

left join lateral .. on true preserves polygons not containing points.

in cases there chance max values identical, alphabetically first language picked in example, way of added order by item. if want all languages happen share maximum count, have more:

answer 2.

select h.gid polygon, c.ct, c.language   hexagonslan h left   join lateral (    select c.language, count(*) ct         , rank() on (order count(*) desc) rnk      points_count c     st_within(c.geom, h.geom)     group  1    ) c on c.rnk = 1 order  1, 3  -- language additional sort critieria

using window function rank() here, (not row_number()!). can count or points and ranking of count in single select. consider sequence of events:


Comments

Post a Comment

Popular posts from this blog

Java 3D LWJGL collision -

-
i making 3d java game lwjgl library, , wondering how add collision detection, player not go through models. i using obj models. here objloader class, loads models: package renderengine; import java.io.bufferedreader; import java.io.file; import java.io.filenotfoundexception; import java.io.filereader; import java.util.arraylist; import java.util.list; import models.rawmodel; import org.lwjgl.util.vector.vector2f; import org.lwjgl.util.vector.vector3f; public class objloader { public static rawmodel loadobjmodel(string filename, loader loader){ filereader fr = null; try { fr = new filereader(new file("res/"+filename+".obj")); } catch (filenotfoundexception e) { system.err.println("couldn't load file!"); e.printstacktrace(); } bufferedreader reader = new bufferedreader(fr); string line; list<vector3f> vertices = new arraylist<vector3f...
Read more

spring - SubProtocolWebSocketHandler - No handlers -

-
i have ugly error during deploying spring app on jboss. 18:11:16,025 error [org.apache.catalina.core.containerbase.[jboss.web].[default-host].[/consumer]] (msc service thread 1-7) exception sending context initialized event listener instance of class org.springframework.web.context.contextloaderlistener: org.springframework.context.applicationcontextexception: failed start bean 'subprotocolwebsockethandler'; nested exception java.lang.illegalargumentexception: no handlers @ org.springframework.context.support.defaultlifecycleprocessor.dostart(defaultlifecycleprocessor.java:176) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor.access$200(defaultlifecycleprocessor.java:51) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.springframework.context.support.defaultlifecycleprocessor$lifecyclegroup.start(defaultlifecycleprocessor.java:346) [spring-context-4.1.3.release.jar:4.1.3.release] @ org.s...
Read more

methods - python can't use function in submodule -

-
my folder structure this: pythonstuff/ program.py modulea/ __init__.py foo.py bar.py here's code bar.py : def hello(): print("hello, world!") here's code program.py : #!/usr/bin/env python3 modulea import bar bar.hello() i run $ python3 program.py somehow error: file "program.py", line 3, in <module> bar.hello() attributeerror: 'module' object has no attribute 'hello' edit: __init__.py file empty. edit2: after trying realized had bar.py in root directory contained hello() method. bar.py in modulea/ directory empty. add it import os __all__ = [] module in os.listdir(os.path.dirname(__file__)): if module != '__init__.py' , module[-3:] == '.py': __all__.append(module[:-3]) the init .py files required make python treat directories containing packages; done prevent directories common name, such string, unintentionally hiding v...
Read more