Is there a possible way to connect to a local SQL server on a static global IP? Let me elaborate. Assume that there is a server with a static global IP of 12.345.678.90 which refers to a certain windows based PC which in turn hosts a Microsoft SQL Server. Said server can be accessed remotely. The issue is the need to connect to another SQL server hosted on another PC on the same network with a "static local IP" of 192.168.1.100. Is there a way to connect to that server using a similar syntax to 12.345.678.90/192.168.1.100?

I need to create a data model for routing data between points. For example, path between two cities which will include the two start and destination cities, way points (like smaller towns along the way), total distance between the two cities (distances between routing points do not need to be modelled though it would be good if that can be done too). My current idea is like below. - Create one table containing the cities: city. Columns: city_id (primary key), city_name, etc. - Create a many-to-many table path with 4 columns: from_city (references city.city_id, to_city (references city.city_id), calculated column path = MD5(CONCAT(from_city, to_city)), distance to store the total distance for the route. - Create a table to store route points called town similar to the city table. - Create a man-to-many table route between path and town which will have three columns: town_id (references town.town_id), path_id (references path.path) and route_position which will be a value 1-n which will indicate the position of the route point in the actual path. For example, first route point will have number 1, second 2 and so on. Questions: 1. Do you guys think this is a feasible model? Is there a better approach to this? 2. In the path table, is it better to use a MD5 hash or create a multiple-column index on from_city and to_city? EDIT: To give some context, I'm currently using a graph database to store this data and have a user-defined function that uses A* search to find the shortest path between points. Problem is as the graph gets denser, this computation becomes too slow. So, I think a lookup-based approach would be faster.

I have some query as select x1,x2,x3,x4 from foo where bar resulting something like: CREATE TEMP TABLE foo AS SELECT * FROM ( VALUES ( '00','01','03','AA' ), ( '01','03','03','BB' ), ( '02','09','05','CC' ), ( '03','08','05','DD' ), ( '03','08','03','EE' ), ( '03','08','03','FF' ), ( '03','08','03','FF' ), ( '03','08','03','FF' ), ( '03','08','09','GG' ) ) AS t( x1, x2, x3, x4); Note the x3 column: we have 03 twice then 05 twice then 03 again then 09. I need to calculate a route using the pgr_ksp function (PostGIS). Imagine the table here is the result and the x3 column is the road names. In this case I start on 03 street, walk on it for 2 segments and take the 05 street just to take the 03 street again after. My problem is I'm showing this segments to the user and I don't like to. The user know nothing about segments of roads and just want to see take 03 street, 05 street, 03 again and then 09. If I just group by, I will lost the take 05 step between the two 03 and I'll see the all long 03 segments connected: take 03 street (all 6 segments together), 05 and then 09 and this is wrong. My question: how can I group the x3 column as the two first 03, then group the two 05 and then group 03 again after 05 and then group all tuples left using this method. Not so fast! I must have a sum of the x2 column as I group the x3. This will be the geometry column I must compute - concat all segments geometry to have the complete way representation. This is the result I want: ----------- | x2 | x3 | |----|----| | 04 | 03 | |----|----| | 17 | 05 | |----|----| | 32 | 03 | |----|----| | 08 | 09 | ----------- x2 is the way geometry and x3 is the name of the street or way. In this case I'll have the complete way segment (sum of small pieces) and the way name.

I am developing the backend for a service that resembles Uber in some aspects (just a simplification to give you the general context). Users will book rides and as such users will define the pickup and drop-off locations. These locations will be addresses in a city (with the possibility of multiple cities in the future). The locations I will receive from the frontend/app will be in the form of latitude, longitude, and google-maps-verified address. I am wondering whether I should store the locations as a separate table, or whether I should embed the locations in whatever table I need them. For example, the bookings table will have a pickup location and a dropoff location. Should these be just references to a locations table, or should I add more columns to store the location right there? I will need at least 3 columns per location (lat, long, address string), with the possibility to have more columns if we decide to break the street address into more components. The way I see it the two approaches trade-off memory and time performance: - A separate table will save some space, as we avoid copying the same information multiple times. But I am not sure how much space will be saved as we do not have enough real data yet. My ballpark estimate is that a medium-sized city has tens of thousands unique addresses. So if we take 100 ride bookings there is little chance for overlap. If we have 100,000 ride bookings there is bound to be considerable overlap. However, space is not really a limiting factor in our design, even with millions of bookings. It does not really matter if we spend 100MB more in our database. - A separate table will make processing slower. First of all, each time we get a booking request we will have to search if the locations in the request already exist in our database. If not, we add them, otherwise we use the existing reference. With potentially tens of thousands addresses in our database this will take some time. Is this extra time significant compared to our other time consuming tasks? An example of another time-consuming task is a call to the google-maps api to find routes from one location to another and get a travel time estimate. I believe this operation will take longer that searching in our database. So it might make little sense optimising the database access when we have other bottlenecks. How about other operations? Say we want to find bookings within a bounding [lat, long] box. This can become more time-consuming with a separate table as we'll need a full table join. Or do a cumbersome two-step search where we find the locations IDs in our table that are within the bounding box and then search the bookings table with these IDs. If we have hundreds or thousands locations in a bounding box this might not be a good way to do it. But again, I am not sure about the utility of trying to optimise this operation. I am not sure how often will I need to search by location. It seems not that often, so I will not gain/lose much one way or the other. It seems to me that there are no strong arguments based on the space-time tradeoff. Are there other aspects I should consider? For example: clarity of design, ease of development, flexibility and expandability. The separate table approach seems to offer more structure and the opportunity for more flexibility if the model needs to change. For example, it might be beneficial to break the street address in multiple components (street, city, state). We are less likely to do this if we are to use location columns in other tables (we need to use at least 3 columns already, and breaking the address in 3 components, brings this number to 5). About flexibility: in the future, we might have multiple-point rides. Having a location table will help with that. I am leaning towards a separate table, but I do not see strong arguments for or against it. Are there other arguments that I am missing? Not sure if the following details are relevant but I will be using postgresql as the RDBMS and sqlalchemy as an ORM to query the database.