How to render a portion of spatial data depending

Question

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 Example Instead of one giant texas table how about have East Texas, North Texas,...etc

Answer 1

There are several ways of improving performance:

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

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

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

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

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

Answer 2

I don't think you need to split the data across multiple tables. What you need to do however, is add spatial indexes to your tables. When querying the database, geoserver will constrain the sql query with a spatial filter (most likely a rectangle in the coordinate system of your spatial data. If the table lacks a spatial index, that will result in a table scan (the entire table is scanned to find out which segments of the streets are within the bounds/spatial filter specified in the query). With a spatial index, only the indexing units crossing the bounds will be looked at, and these are considerably less than the entire table.

Assuming your spatial data is stored in sql-server, this link, might give you a starting point on spatial indexing with an sql-server engine.