More

Find point close to points from postgis table

Find point close to points from postgis table


I have a postgis point table with a large list of cities.

I need to find duplicates in it, but only if they are 10km close each other, which means that is the same city.

For example, I have 5 points called 'Eneby', and suppose that 4 of this are 10Km close each others, and the 5th is 300Km away, what will mean that is another Eneby town. The query in this case should only have to show 4 points.

So, I've made this query, but is not showing the expected result:

Select p1.id, p2.id, * from points as p1, points as p2 where p1.name='Eneby' and st_dwithin(p1.geom,p2.geom,0.1, true) and p1.id=p2.id

The result is the same 5 points, no matter what distance (in degrees) I set. I'm trying with different distance values, but is always the same result.

Does anybody know if is there any mistake in this query?

I've been looking through this forum, and found some similar questions, but can't find exactly this problem.


Assuming that id is the primary key for your points table, and that you want to group point ids which are 10km apart in order to select a best point from each collection. Note that your distance parameter in this case should be the assumed diameter of your city, not the radius. This code will first, for every point, create an ordered array of points within the diameter sharing a same city name.

WITH point_arrays AS( SELECT p1.name, ARRAY_AGG(p2.id) ORDER BY p2.id FROM points p1, points p2 WHERE p1.name = p2.name AND st_dwithin(p1.geom,p2.geom,0.1, true) AND p1.name = 'NAME' --omit if you want to do this for all city points GROUP BY p1.name)

Then, because of redundancy in the first block, find the distinct array of point ids for every city name.

SELECT DISTINCT name, point_array FROM point_arrays

It might be a solution :

1 create buffers radius 5 for each point, keep attributes ; 2 select buffers intersecting each other with same name ; 3 keep the "best" cities ;

What do you think ?


I think yourp1.id=p2.idis the problem. If you change it top1.id<>p2.idand add ap1.name='Eneby, I think you'll get what you are after.

with cities as ( select 1 ID, 'Eneby' as CityName, ST_MakePoint(10, 10) geom union select 2 ID, 'Eneby' as CityName, ST_MakePoint(11, 11) geom union select 3 ID, 'Eneby' as CityName, ST_MakePoint(11, 12) geom union select 4 ID, 'City B' as CityName, ST_MakePoint(50, 50) geom union select 5 ID, 'City B' as CityName, ST_MakePoint(51, 52) geom union select 6 ID, 'Eneby' as CityName, ST_MakePoint(500, 500) geom ) select ST_DWithin(p1.geom,p2.geom,300) iswithin, ST_Distance(p1.geom,p2.geom) dist, p1.ID,p2.ID ID2, p1.CityName from cities p1, cities p2 where p1.ID<>p2.ID and p1.CityName='Eneby' and p2.CityName='Eneby' and ST_DWithin(p1.geom,p2.geom,300)

And if you wanted to do all the cities, I think you could get all the distances between the points and then group the city names by the name and whether they are withxdistance of one another:

with cities as ( select 1 ID, 'City A' as CityName, ST_MakePoint(10, 10) geom union select 2 ID, 'City A' as CityName, ST_MakePoint(11, 11) geom union select 3 ID, 'City A' as CityName, ST_MakePoint(11, 12) geom union select 4 ID, 'City B' as CityName, ST_MakePoint(50, 50) geom union select 5 ID, 'City B' as CityName, ST_MakePoint(51, 52) geom union select 6 ID, 'City A' as CityName, ST_MakePoint(500, 500) geom ) select a.CityName, within300, ST_AsText(ST_Collect(geom2)) as geomcoll from ( select c1.geom, c2.geom geom2 ,c1.ID, c2.ID ID2, c1.CityName, c2.CityName CityName2, ST_Distance( c1.geom,c2.geom), CASE WHEN ST_Distance( c1.geom,c2.geom) < 300 THEN 1 ELSE 0 END within300 from cities c1, cities c2 where c1.ID <> c2.ID ) a where CityName = CityName2 group by a.CityName, within300

Population’s health information-seeking behaviors and geographic variations of stroke in Malaysia: an ecological correlation and time series study

Stroke has emerged as a major public health concern in Malaysia. We aimed to determine the trends and temporal associations of real-time health information-seeking behaviors (HISB) and stroke incidences in Malaysia. We conducted a countrywide ecological correlation and time series study using novel internet multi-timeline data stream of 6,282 hit searches and conventional surveillance data of 14,396 stroke cases. We searched popular search terms related to stroke in Google Trends between January 2004 and March 2019. We explored trends by comparing average relative search volumes (RSVs) by month and weather through linear regression bootstrapping methods. Geographical variations between regions and states were determined through spatial analytics. Ecological correlation analysis between RSVs and stroke incidences was determined via Pearson’s correlations. Forecasted model was yielded through exponential smoothing. HISB showed both cyclical and seasonal patterns. Average RSV was significantly higher during Northeast Monsoon when compared to Southwest Monsoon (P < 0.001). “Red alerts” were found in specific regions and states. Significant correlations existed within stroke related queries and actual stroke cases. Forecasted model showed that as HISB continue to rise, stroke incidence may decrease or reach a plateau. The results have provided valuable insights for immediate public health policy interventions.


Introduction

The population distribution of genes for an increasing number of autosomal recessive disorders can now be studied directly on the mutational level. New techniques based on the polymerase chain reaction (PCR) have greatly improved the speed and simplicity of mutation detection. Population genetic studies are prolific in particular for diseases like phenylketonuria (PKU), with extensive mutation heterogeneity [1] and many normal markers in the phenylalanine hydroxylase (PAH) gene to provide highly informative RFLP haplotypes [2, 3]. The distribution of 5 prevalent PKU mutations (R158Q, R261Q, IVS10nt546, R408W, and IVS12ntl) throughout Europe has recently been investigated [4]. The strong associations between specific RFLP haplotypes and these PAH mutations suggest that the present distribution in Europe is largely due to founder effects and random genetic drift. However, a more detailed study of the distribution of PKU mutations within a single country might extend this hypothesis. In this respect Norway (4.3 million inhabitants, 324,000 km 2 , 1,752 km measured by its long axis) may be particularly well suited for studies because of its relatively stable population structure for centuries and short average marital distance [5]. Previous observations have suggested that Norwegian PKU mutations might have originated from Celtic populations [6]. Treacy et al. [7] have proposed that PKU mutations I65T on haplotype 9.8 and R408W on haplotype 1.8 are markers for ‘Celtic’ PKU genes. The latter mutation has its peak incidence in the Irish population, but is also common in several other northwestern European populations, including the Norwegian. This distribution may be compatible with the hypothesis of a ‘Celtic’ PKU allele [8]. We have previously reported the identification and characterization of the ‘Norwegian’ PKU alleles G272X [9, 10], F299C [11], R408Q [11], and G46S [12] as well as a number of singly occurring alleles [13–16]. The mutation analysis is here extended to 236 PKU alleles. We have combined results from the mutation analysis with those from PAH haplotype analyses, and looked at the geographic backgrounds of mutations and haplotypes two generations back in order to map the distribution of each PKU mutation in Norway.


Bibliometric visualisation: an application in tourism crisis and disaster management research

A limited number of studies have applied bibliometric visualisation to explore the network structure of scholarly tourism knowledge. This study uses CiteSpace to analyse and visualise the intellectual structure of the tourism crisis and disaster management (TCDM) field. The use of new bibliometric visualisation techniques makes a methodological contribution to the mapping and presentation of bibliometric data in tourism research. Potentials for using these methods to provide new insights into research patterns and gaps are illustrated with an analysis of the TCDM literature. The study demonstrates how bibliometric visualisation can provide new insights into an area of literature by better communicating key findings, facilitating the exploration of data, and providing rich information to readers. Findings indicate that TCDM research has moved from broader topics to more specific issues, with a more recent focus on resilience and economic crises. The visualisation of co-authorship networks reveals that major collaborative networks are based on geographic and institutional proximity, dominated by scholars in the United States, United Kingdom, and Australia. Seven major research clusters are identified from the visualisation of a co-citation network. The identification of structural holes and bridging papers draws attention to research gaps and future research opportunities in the TCDM field.


Modeling on management strategies for spatial assessment of earthquake disaster vulnerability in Bangladesh

This paper explores sustainable management strategies for mitigation destructive geological disaster earthquake spatial susceptibility in Bangladesh, is located south piedmont plain of Himalaya mountain ranges in South Asia. During the contemporary time, earthquake is regarded as most destructive geological disaster due to its devastating impact on physical environment, socio-economic infrastructure, people’s lives, livelihood and biodiversity of the world. In Bangladesh, earthquake destruction vulnerability is the results of two major factors natural (geological formation, plate tectonic framework, plate margin, geographical location, location of fault and fold) and human intervention (tremendous increase of population, construction of multi-storied concrete building in urban environment, rapid growth of urbanization and industrialization). Both physical and manmade factors accelerated extreme earthquake disaster which impacts on sustainable life style and livelihood patterns of poor peoples as a developing nation Bangladesh of the world. To ensure sustainable management strategies of earthquake spatial susceptibility, the aim of this paper is selected to access earthquakes susceptibility using geostatistical modeling for producing detailed risk to support mitigation approach of earthquakes in present and future in Bangladesh. For this study, almost 94 earthquakes samples are collected from Google Earth during the time period (1961–2018) in inland Bangladesh. In this study, spatial susceptibility map developed by applying geostatistical models with GIS approach which based on earthquakes magnitude (Richter scale) and focus depth (km). Predictive spatial earthquake susceptibility map and risk estimation process will help geologist, geomorphologist, environmental engineers, urban planner, government and non-government organizations for vulnerability assessment including structural (settlement planning, building materials and building code development) and nonstructural mitigation (disaster preparedness approach: pre-disaster, during disaster and post-disaster), spatial risk estimation, disaster crisis and conflict mitigation, relief distribution at national and global level.

This is a preview of subscription content, access via your institution.


Geographic variation of contact calls suggest distinct modes of vocal transmission in a leaf-roosting bat

Populations that have historically been isolated from each other are expected to differ in some heritable features. This divergence could be due to drift (and other mechanisms of neutral evolution) or differential adaptation of populations to local conditions. Discriminating between these two evolutionary trajectories can be difficult, but when possible, such data provides critical insight into the evolutionary history of a species. Here, we examine the patterns of geographic variation of two contact signals regularly produced by disc-winged bats, Thyroptera tricolor, and discuss possible processes leading to the observed patterns of differentiation. We compared allopatric populations separated by an elevational barrier, and estimated genetic distance using nuclear microsatellite loci. Our findings revealed that the mountain ridge is permeable to gene flow. Acoustic divergence was significantly explained by genetic and spatial factors, supporting the notion that stochastic factors are the main drivers of signal divergence. Yet, we found different patterns of geographic variation between the two types of calls. We examine how this variability in the patterns of acoustic divergence may suggest distinct modes of signal transmission within and between populations (i.e., social vs genetic transmission). This work provides further support of the role of random change shaping communication systems in mammals, and highlights the importance of studying multiple elements of a species repertoire to evaluate the underlying processes driving signal evolution.

Significance statement

Despite increasing interest in studying patterns of acoustic divergence, the relative contribution of adaptive and stochastic processes underlying variation of acoustic signals remain poorly understood, particularly in mammals. Our study examines signal divergence in Spix’s disc-winged bats, Thyoptera tricolor, with the goal of understanding the underlying processes driving signal evolution. Specifically, we studied whether the patterns of geographic variation of two social signals regularly emitted by T. tricolor are congruent with patterns of genetic distance among populations separated by a geographic barrier. We demonstrate that genetic and spatial distance explains acoustic variation, which points to stochastic processes as major drivers of signal divergence in T. tricolor. Notably, we found that the patterns of geographic variation differ between the two types of calls studied. We suggest that this variation results from distinct modes of vocal transmission within populations. Comparison of different signal types provides additional insight of social pressures shaping call design.


Extended Data Fig. 1 Spatial versus Aspatial Exposure/Isolation.

Nationwide distribution (n = 180,660,202) of individual spatial (left) and aspatial (right) partisan isolation and exposure separately for Democrats (blue) and Republicans (red). Solid vertical lines represent mean values and dashed lines represent median values. The distributions are weighted by the posterior partisan probabilities.

Extended Data Fig. 2 Individual Differences in Spatial versus Aspatial Exposure/Isolation.

Nationwide distribution (n = 180,660,202) of individual-level changes in partisan Exposure and Isolation separately for Democrats (blue) and Republicans (red). The histograms on the left show the percentage point difference in spatial and aspatial exposure, while the histograms on the right show the percent change. Solid vertical lines represent mean values and dashed lines represent median values. The distributions are weighted by the posterior partisan probabilities.

Extended Data Fig. 3 Individual Absolute Differences in Spatial versus Aspatial Exposure/Isolation.

Nationwide distribution (n = 180,660,202) of individual-level absolute changes in partisan Exposure and Isolation separately for Democrats (blue) and Republicans (red). The histograms on the left show the percentage point absolute difference in spatial and aspatial exposure, while the histograms on the right show the absolute percent change. Solid vertical lines represent mean values and dashed lines represent median values. The distributions are weighted by the posterior partisan probabilities.

Extended Data Fig. 4 Exposure and Isolation with Imputation Versus Without Imputation.

Nationwide distribution (n = 180,660,202) of individual spatial partisan isolation and exposure with imputation of partisanship (left) and without (right) separately for Democrats (blue) and Republicans (red). Solid vertical lines represent mean values and dashed lines represent median values. The distribution on the left is weighted by the posterior partisan probabilities.

Extended Data Fig. 5 Percent self-report Partisan Category by Posterior Partisan Probability.

LOESS lines plotting the relationship between posterior partisan probability (Republicans on top, Democrats on bottom) and the rates of survey respondents reporting as the corresponding partisanship. The correlation is limited to the subset of survey respondents (n = 7, 087) who are not registered with a major political party. Black lines plot the LOESS curve with survey weights incorporated, red/blue lines without survey weights. The 45-degree grey line plots a perfect 1-to-1 relationship between posterior partisan probability and self-reported partisanship. The horizontal dotted lines show the rates at which survey respondents who are registered Democrats/Republicans self-report partisanship in agreement (or disagreement for the lower lines) with their actual partisan registration. That is, the upper blue (red) dotted line represents the proportion of survey respondents we know are registered Democrats (Republicans) who self-report as Democrats (Republicans), and the lower dotted line represents the proportion who do not self-report as Democrats (Republicans). These lines represent lower and upper bounds on how accurate we can expect our forecast to appear when measured against survey data. The histogram on the bottom plots the frequency distribution of posterior partisan probabilities across the unaffiliated subset.

Extended Data Fig. 6 Partisan Segregation vs. non-Hispanic White-only Partisan Segregation.

Distribution for non-Hispanic white voters (n = 115,736,045) of differences between partisan segregation calculated from all 1,000 nearest neighbors and partisan segregation calculated only from non-Hispanic white neighbors. Positive Isolation values means that a voter appears less isolated by partisanship when we look only at their non-Hispanic white neighbors. Positive Exposure values means that a voter appears to have less cross-party exposure when we only look at their white neighbors. Distributions are plotted separately for Democrats (blue) and Republicans (red). Solid lines represent mean values and dashed lines represent median values. Distributions are weighted by posterior partisan probabilities.


A framework for annotating OpenStreetMap objects using geo-tagged tweets

Recent years have witnessed an explosion of geospatial data, especially in the form of Volunteered Geographic Information (VGI). As a prominent example, OpenStreetMap (OSM) creates a free editable map of the world from a large number of contributors. On the other hand, social media platforms such as Twitter or Instagram supply dynamic social feeds at population level. As much of such data is geo-tagged, there is a high potential on integrating social media with OSM to enrich OSM with semantic annotations, which will complement existing objective description oriented annotations to provide a broader range of annotations. In this paper, we propose a comprehensive framework on integrating social media data and VGI data to derive knowledge about geographical objects, specifically, top relevant annotations from tweets for objects in OSM. We first integrate geo-tagged tweets with OSM data with scalable spatial queries running on MapReduce. We propose a frequency based method for annotating boundary based geographic objects (a polygon), and a probability based method for annotating point based geographic objects (Latitude and Longitude), with consideration of noise. We evaluate our methods using a large geo-tagged tweets corpus and representative geographic objects from OSM, which demonstrates promising results through ground-truth comparison and case studies. We are able to produce up to 80% correct names for geographical objects and discover implicitly relevant information, such as popular exhibitions of a museum, the nicknames or visitors’ impression to a tourism attraction.

This is a preview of subscription content, access via your institution.


Evaluating Colorado’s Essentials for Childhood Project through a Collective Impact Lens

This case study examines the development of the Colorado Essentials for Childhood project, a collective impact effort to prevent child maltreatment, over a five-year period (September 2013–August 2018). We conducted semi-structured key informant interviews with 26 project stakeholders to understand how the project’s priorities evolved, along with the challenges it enountered and the success it achieved. Interviewees included members of the Leadership Action Team, Centers for Disease Control and Prevention representatives, and staff employed by the project during its entire history. Enabling authors to use NVivo 12 to organize and code interview transcripts, a transcription service transcribed each recorded interview. This paper summarizes the Colorado experience in the context of the five conditions for collective impact success (a common agenda, shared measurement systems, mutually reinforcing activities, continuous communication, and backbone support organizations) with the intent of informing other similar efforts. The review of this evaluation offers recommendations for future directions and how to overcome similar challenges in implementing a collective impact approach, particularly in a limited resource environment.

This is a preview of subscription content, access via your institution.


Regional disparities in primary school participation in developing countries

Education for All has focused international attention on the goals of universal primary education and improved education quality. However, national indicators related to these goals often mask significant differences among demographic and social groups, as well as among geographical regions within countries. This paper, based on a study commissioned by UNESCO’s Global Monitoring Report team, examines within-country (regional) disparities in participation in primary education in between 55 and 60 countries in sub-Saharan Africa, Asia and the Pacific, Latin America and the Arab States. After reviewing the methodology used in the analysis, the paper compares countries’ disparities in net enrolment rates before and after the Dakar Framework was established in 2000, changes over the pre- to post-Dakar period and a comparison of net enrolment rates with pupil-teacher ratios—one of the standard measures of education quality. Overall, the analysis finds significant differences in the magnitude of regional disparities in primary participation across the countries, with the smallest disparities in Latin America and the largest in sub-Saharan Africa. While just over half the countries with both pre- and post-Dakar data showed improvements over the period, there was little change in countries’ rankings on the disparities measures over this period.

This is a preview of subscription content, access via your institution.


Watch the video: Geometry Creation and Manipulation Using PostGIS. PostGIS Baby Steps