问题描述
我们在数据库中有每条得克萨斯州的街道.问题是，有200万条街道.当用户在地图上查看一个简单的县时，由于查询必须经过得克萨斯州表中的每个街道，因此永远需要为该县渲染街道.有人对我们如何在表之间拆分数据以及让Geoserver根据他们正在查看的当前用户位置查询所需的表有任何建议吗?
we have every street for texas in a database.The issue is, there are 2million streets. When the user looks at a simple county on the map, it takes FOREVER to render the streets for the county because the query has to go through EVERY SINGLE STREET in the texas table. Does anyone have any advice on how we can split the data across tables and have geoserver query the table needed depending on the current users location they are viewing?
例如而不是一张大得克萨斯州桌子，东德克萨斯州，北德克萨斯州等等...
For ExampleInstead of one giant texas table how about have East Texas, North Texas,...etc
推荐答案
有几种提高性能的方法:
There are several ways of improving performance:
-
如果尚未创建空间索引，则需要创建它们.但是，根据表的大小和请求数据的方式，性能提升可能不够大.
If you already have not created spatial indexes, you need to create them. However depending on the size of your table and the way you are requesting data it might not be large enough performance increase.
分解数据或添加其他限定符.例如，修改您的表以包括邮政编码，县名等.然后，当您需要请求特定县时，请直接进入该表.不要忘记在创建的任何新列上都包含索引.
Break apart your data or add additional qualifiers. For example, modify your table to include postal codes, county names and etc. Then when you need to request specific county you just going straight to it. Don't forget to include indexes on any new columns you create.
将违抗数据分离到单独的表中.例如，在我的公司中，我们正在处理邮政编码，即由多个邮政编码组成的州区域.拥有一个或多个州辖区的地区.最后是拥有多个区域的领土.为了使邮政编码更快，州区，地区和地区位于单独的表格中.每当我们需要加入它们时，我们都有一个仅包含键的交叉引用表.
Separate your defying data into separate tables. For example in my company we are dealing with postal codes, state zones that comprise of multiple postal codes. Than regions that have one ore more state zone. Finally territory that hold multiple regions. To make it faster postal codes, state zones, region and territory are in separate tables. When ever we need to join them we have cross reference table that only holds keys.
仅在需要时返回地理空间数据.例如，在我们的示例中，每个表中都有地理空间数据，但是在获取数据时，我们仅返回所需的空间数据，并且仅返回其他表中的键和其他非空间数据.
Only return geo spatial data when you need it. for example in our case we have geo spatial data in each table but when getting data we only return the spatial data we need and only keys and other non spatial data from other table.
在数据元素周围构建矩形，例如，县是不规则形状，但是如果使用GEOMETRY数据类型，则可以使用STEnvelope函数在其周围构建框.通过该功能，您可以获取东，西，南和北点.一旦有了这些点，就可以将它们存储在其他表中，而不是需要确定用户所在的特定区域时.当您获得纬度/经度位置时，可以在使用STWithin或STIntersect函数确定要返回的区域之前，使用这些矩形限制结果的数量.
Build rectangles around your data elements, for example county is irregular shape but if you use GEOMETRY data type you can use STEnvelope function that builds box around it. From that function you can get your East, West, South and North points. Once you have those points you can store them in other table and than when you need to figure out specific area where user is. When you get Lat/Long position you can use those rectangles to limit number of results before using STWithin or STIntersect function to figure out for sure the area that you need to return.
底线请记住，以减少您必须查看并返回的数据量.您可以减少需要评估的行数或行的速度越快，查询就会越快.
Bottom line remember to reduce amount of data you have to look at and return. The faster you can reduce number or rows that need to be evaluated the faster your queries will be.
这篇关于如何根据用户在地图上的位置渲染部分空间数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！